# Mix VLOOKUP with Data Validation for some magic! [VLOOKUP Week]

Posted on November 1st, 2010 in Learn Excel - 19 comments

### Situation

Sometimes we don’t know what we want. If this happens when I am in a bar, I usually order a cocktail. Just a mix of everything. The same will work in Excel too.

For eg. If you have lots of data, but the value you want to look up needs to change based on whims and fancies of your users, then you can resort to a cocktail. A mix of VLOOKUP with Drop down lists (Data validation)

Data:

### Solution

The recipe for VLOOKUP cocktail is relatively simple. We just take the list of sales person names and use it as a source for our input cell’s data validation drop down list. Rest is left to your imagination. Here is an example in action.

Examples:

### Sample File

Download Example File – Mix VLOOKUP with Data Validation for Some Magic

### Similar Tips

 Making VLOOKUP formulas go wild [VLOOKUP Week] How to Lookup Values to Left?
 Written by Chandoo Tags: data validation, downloads, drop down lists, iferror, Learn Excel, Microsoft Excel Formulas, rank(), screencasts, spreadsheets, vlookup, vlookup week Home: Chandoo.org Main Page ? Doubt: Ask an Excel Question

### 19 Responses to “Mix VLOOKUP with Data Validation for some magic! [VLOOKUP Week]”

1. dan l says:

Oooooo and don't forget: Using an identical method, one can build a dynamic chart.....

2. Gregory says:

This is a classic use of the VLOOKUP function, that of being tied to a drop-down list with data validation.

I learned about he RANK Function and while looking it up in Help, noticed the newer functions RANK.AVG and RAND.EQ which seems to be overkill.

I would have probably used H7=0 instead of NOT(H7) simply because of less typing, but hadn't considered using the NOT function in that situation. Interesting.

3. baynoli says:

hi chandoo,
im currently practicing excel formulas especially doing some reports for my boss.
I really find your website informative, really appreciate you put effort on helping
newbie like us understand better.
Anyway, I tried to explore this Vlookup example and made me scratch my head.
pasted below is your example, my question is that re:tblData and valSalesPerson which i wonder how the it directs to the data table or certain cell.
Also how to write a cell number in a formual if that cell is being merge like in your example.

thanks.
baynoli
=IF(valSalesPerson=\$B\$4,0,VLOOKUP(valSalesPerson,tblData,3,FALSE))

4. Surbhi Gupta says:

Good Morning,

5. Hui... says:

@Surbhi have a look at http://chandoo.org/wp/tag/vlookup-week/

6. PM says:

@Chandoo:
Does Vlookup only work when we try to find the value linked to the 1st column of an array?

I have the following columns and I am trying to use the same format you have above to showing clients values for thier stocks.
1. Scrip Number
2. Scrip Name
4. Sell Px

Now I want the client to have a drop down of the Scrip Name (column 2), but when I try to use what you have shown above to display say the Buy Px and Sell Px and Profit the values dont show up.

The way it does work is if i define my array to start from Scrip Name instead of Scrip Number.

I am wondering if there is another way besides to the patch I just made.

Thanks
PM

7. Hui... says:

@PM
You don't have to include your First Column in the array, leave it out
Then Column 2 will be the first column, Buy will be Col 2 and Sell Col 3

8. Rajesh Joshi says:

Hi,

I know basic excel. I downloaded this file and try to make a similler for my data ..but it dosent work. Is there a vb code , because when i type =if(it shows a valSalesPerson option in the dropdown. How can i make it work for my data.

Thanks
Rajesh

9. Hui... says:

@Rajesh

There is no VBA in that file
valSalesPerson is a Named Formula which refers to G5
that is if you select the cell in G5 and select a value from the validation drop down
the Named Formula valSalesPerson will be equal to that Name and can be used elsewhere

10. Rajesh Joshi says:

Thanks , it helped.
Great site for excel lovers .

11. Sandra says:

I have a question on the formula used. Why is the IF part needed here: =IF(valSalesPerson=\$B\$4,0,VLOOKUP(valSalesPerson,tblData,2,FALSE))

If I remove that and leave only =(VLOOKUP(valSalesPerson,tblData,2,FALSE)
it will work as well, so not sure whay the IF part was added?

thanks.

12. nuxie says:

Thank you, i'm really appreciate that

13. Sachin Kumar says:

hi chandoo,
im currently practicing excel formulas especially doing some reports for my boss.
I really find your website informative, really appreciate you put effort on helping
newbie like us understand better.
Anyway, I tried to explore this Vlookup example and made me scratch my head.
pasted below is your example, my question is that re:tblData and valSalesPerson which i wonder how the it directs to the data table or certain cell.
Also how to write a cell number in a formual if that cell is being merge like in your example.
thanks.
sachin
=IF(valSalesPerson=\$B\$4,0,VLOOKUP(valSalesPerson,tblData,3,FALSE))

14. YOGESHWAR says:

Hi chandoo.
i have question. i have spreadsheet has lot of date say. Part number and description, i want to do once i type just only part number the descripition auto populated in other cell. please help me thank you

15. krishna says:

Good example, but what happens if there are 2 sales people with the same name it will always pick up the data for the first one.

• Astrid says:

Good Day to you,
These examples contain managable data - inconsistencies such as duplicates in a vlookup are problematic in larger data - as you don't see it. Depending on what your data shows/contains and your main "feature" is vlookup, it is ALWAYS advisable to devise a plausability check - in this case, I would personally use a countif function =COUNTIF(\$B\$5:\$B\$17;B5) - in column A; conditional format in column A to highlight values >0 in Red....

16. Jamal says:

I'm Creating a list of state of india with City belong with State in first cell I use data validation for state list I want city name display in second cell of select state only. is this possible please help.

17. Hameed says:

If find list Dept . i need select of dept appear names employees
Why is it?
PLEZ

18. VISHAL says:

Just want to learn what is the use of “” function in Rank of Sale Peson.

Thanks & Regards,
Vishal

 Making VLOOKUP formulas go wild [VLOOKUP Week] How to Lookup Values to Left?