fbpx
Search
Close this search box.

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

Share

Facebook
Twitter
LinkedIn
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

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Overall I learned a lot and I thought you did a great job of explaining how to do things. This will definitely elevate my reporting in the future.
Rebekah S
Reporting Analyst
Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

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,

    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

  17. VISHAL says:

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

    Thanks & Regards,
    Vishal

Leave a Reply