How JustAnswer Works:
  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.
Ask Richard Your Own Question
Richard, Software Specialist
Category: Microsoft Office
Satisfied Customers: 36575
Experience:  Over 15 year experience resolving Microsoft Office Issues
Type Your Microsoft Office Question Here...
Richard is online now
A new question is answered every 9 seconds

Hi,im looking for a formula that works like a vlookup but

This answer was rated:

Hi, im looking for a formula that works like a vlookup but want it to return different values based upon the occurences in the array. I also do not want it to duplicate values. Any suggestions?

Thank you for your question, my name is Richard.

Can you tell me please with a bit more detail what you mean by different values based upon the occurrence in the array?

Do you mean you want it to find a criteria within an array of cells?

Thank you
Customer: replied 3 years ago.

Hi Richard to give you an example of what i am working on. In one tab of my worksheet, i have a "control library" where multiple different controls are mapped to different risks. One control is documented per row, and are assigned a risk, rating, etc.


in my next tab i want to creat a funtion that lists all the controls from the control library that are mappped to a specific risk. So in my example there can be 5 controls that are mapped to 1 specific. When i talk about different values basically i want to have one unique control instance per risk.

Can I look at the spreadsheet please Bill?

- Click the following link:
- Upload the file to that website
- Once it is uploaded, the resulting page will display a "File ID" number.
- Please, give me that "File ID Number"

Thank you
Customer: replied 3 years ago.

Im working from my work computer so unfortunately I cant share the spreadsheet due to securitiy controls. But i am basically building a worksheet from scratch. Hopefully i can explain what i am trying to do here it is


The controls library tab has the following columns listed below. each row in the this tab represents 1 unique control so there are no duplicates


Risk Level 1

Control Name

Unique Conrol ID

Control Assessment Rating


Now in my 2nd tab i want the formula to return all the control names that are mapped to the risk level 1 as i need to list all the controls by Risk Level 1


Thanks Bill,

I believe this is what you want.

It will find the occurrences, and will not repeat them if you paste down the formula.

Go to

Click Download and download File ID 520800

Let me know if this is what you want please.

Thank you
Customer: replied 3 years ago.

Hi Richard i didn't see an excel file where in the zip drive should l look

No zip drive Bill

I think you have an older version of Excel, which shows it as a Zip.

I have saved it as an older version.

Download it from with File ID 445380

Let me know if you have any difficulties please

Thank you
Customer: replied 3 years ago.

Hi Richard,


Its getting there but i have some questions,


what does the RoW(1:1)),2 do at the end of the equation. My sheet is set up a little different,y

Row(1:1) is for returning the row number and is part of the function

2 at the end returns column 2 from the array I set which is Sheet1!$A$1:$B$5

It returns the column in Column B as this is the second column (as specified at the end of the function).

You also need to press CTRL+Shift+Enter when saving the forumla, not just Enter, as it is an array formula

Customer: replied 3 years ago.

Richard its not working?


=INDEX(Controls Library!$G$2:$R$452,SMALL(IF(Controls Library!$G$2:$G$452='RCSA Unit 1 Risk Assessment'!A8),ROW('Controls Library'!G2:G452)),ROW(1:1)),4)

I need to see the sheet Bill.

Can you not take out the important data? and make a sample?

Else I cannot know why it is not working.

Make a sample, I only care about the structure of it and some small data.

Then - Click the following link:
- Upload the file to that website
- Once it is uploaded, the resulting page will display a "File ID" number.
- Please, give me that "File ID Number"

Thank you
Customer: replied 3 years ago.

its not the issue with data as there is none, the upload from my cpu has been disabled.

What was the problem you got with the formula?

Did you press SHIFT+CTRL+ENTER when saving it? And not just enter?

What error do you get when you try to upload the file?

Customer: replied 3 years ago.

It said i couldn't copy the formula over. I tried to enable editing to allow me to save and edit, but it said i incurred an error. So i had to input by hand.


do the {} need to be outside of the first equal sign. Nothing happens when i do that. Also when i try to enter the formula it looks as though it highlights the Yes reference on your example which i overrided with a cell reference which is RCSA UNit 1 Risk Assessment'!A8.

You dont enter in the {}

you press CTRL+SHIFT+Enter when you finish editing the formula, to save it.

But what happens when you try to upload the file to

Richard and 2 other Microsoft Office Specialists are ready to help you
Customer: replied 3 years ago.

when i try to edit the file it says it does not exist. the uploading perspective has been disabled from my CPU


Can I log on and look please on the connected computer?

It is easier.

Go to Click Me


It will download a program to your computer

Run the program by double clicking it, and it will give you a set of 9 numbers

Give me the numbers at the top of the screen (eg: 456-789-123) in your message to me here please

Thank you
Customer: replied 3 years ago.

Sorry richard. I work for a bank, and don't have the ability to download programs. Security issues. i just need to fix this excel issue. not sure if we can take this any further unless you can walk me through it via text.

It makes it hard Bill if I cannot see how your sheet is structured.

Look at my Formula.


I will break it down


This sets the Data Array, so this needs to be covering the Criteria Column and the Value it returns.


This will make sure there are no duplicates when you are copying the formula down.


This will say all Cells in Column A (the criteria cell) = yes


This returns the row number when the criteria is matched.


This returns the value in Column 2 (so in this case B)

When you write the formula, make sure you press SHIFT+CTRL+ENTER to save it, not ENTER.

If you use my Formula correctly, and match it to your rows, it will work Bill, just make sure it is matching correctly with your data please.

Customer: replied 3 years ago.

I think the probel is with the IF portion of the formula. Rather than setting it to yes, i am setting it to a cell reference that is the criteria for the search

That is no problem Bill.

It can be text or it can be a Cell for use with the criteria

Sp for example


Where E2 holds the value that is being used for the criteria.

Customer: replied 3 years ago.

Its getting there, but the only issue with the formula is that in a working example of this is that don't know which row the criteria match is going to occur in.

Then you need to set it as text Bill, and not a cell reference.

So replace Yes with the criteria


As you would need to search the rows for the criteria, so you would already know it beforehand, so it would be done as text in the formula.

Customer: replied 3 years ago.

I have done that, I think the issue is with the ,ROW(1:1)) because you are telling it which row to look for the value on

No, as this part tells it which Column and Rows to look for the value on


It is looking at Column A, Rows 2-5

Row(1:1) is to make sure it returns unique values.

You need to leave that as it is.

Customer: replied 3 years ago.

I think that this is actually working now. the only think i noticed was that its not picking up the first control. where there would be a hit.

Make sure =INDEX(Sheet1!$A$1:$B$5, starts at the very top (in my example $A$1

Even if thats not in the search criteria, it needs to start at A1 (or the top of the column of what you are specifying) as it counts the rows, so if it finds a match on Row 3, that needs to be 3 from the top.

Customer: replied 3 years ago.

Its working. By George i think its working. Can i copy this formula to other cells??

yes you can.

Just copy and paste the formula like you do any.

Good work Bill!

I am going offline now, as I am 1 1/2 hour over my sign off time (I really wanted to get this solved for you)!

It was tricky, not being able to look at the file, but we got there.

I will be on for 5 more minutes, just in case you have any problems, so please let me know if you do, else if you can please rate my service, that is very appreciated.

Thank you
Customer: replied 3 years ago.

Thank you richard. you were fantastic!!!

Your most welcome Bill! you were just as great!

Remember me if you need anything else.

And all the best.

Take care


Related Microsoft Office Questions