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

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

This article is part of our VLOOKUP Week. Read more.

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:

Data for this Example -Mix VLOOKUP with Data Validation for Some Magic

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:

Data for this Example -Mix VLOOKUP with Data Validation for Some Magic

Sample File

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

Similar Tips

VLOOKUP Week @ Chandoo.org - Learn tips on lookup formulas in Excel

Your email address is safe with us. Our policies

Written by Chandoo
Tags: , , , , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

18 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,

    Please help me in excel over subject vlook up.

  5. 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
    3. Buy PX
    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

  6. 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

  7. 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

  8. 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

  9. Rajesh Joshi says:

    Thanks , it helped.
    Great site for excel lovers .

  10. 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.

  11. nuxie says:

    Thank you, i’m really appreciate that

  12. 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))

  13. 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

  14. 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….

  15. 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.

  16. Hameed says:

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

Leave a Reply