VLOOKUP Example: Vlookup with a Drop Down List

Author: John Michaloudis - Posted on February 26, 2021

Home » Excel Formulas » Lookup » Vlookup » VLOOKUP Example: Vlookup with a Drop Down List

>> FREE DOWNLOAD <<
THE ULTIMATE EXCEL FORMULAS GUIDE!

What does it do?

Searches for a value in the first column of a table array and returns a value in the same row from another column (to the right) in the table array.

Formula breakdown:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

What it means:

=VLOOKUP(this value, in this list, and get me value in this column, Exact Match/FALSE/0])

The VLOOKUP function in Excel can become interactive and more powerful when applying a Data Validation (drop down menu/list) as the Lookup_Value. So as you change your selection from the drop-down list, the Excel VLOOKUP value also changes.

Table of Contents

See how easy it is to apply this with Excel dependent drop down list VLOOKUP example below…

👉 Click Here To Join Our Free Formulas & Functions Webinar Training on VLOOKUP, IF, SUMIF & INDEX/MATCH & Advance Your Excel Level!

Follow the step-by-step tutorial on Excel dependent drop down list Vlookup and download this Excel workbook to practice along

DOWNLOAD EXCEL WORKBOOK

Watch our free training video on how to use Vlookup with a Drop Down List

DOWNLOAD OUR
FREE EXCEL GUIDES

STEP 1: Go to Data > Data Validation.

STEP 2: Select List in the Allow dropdown.

For the Source, ensure that it has the 4 Stock List values selected. Click OK.

Your dropdown is ready.

STEP 3: We need to enter the Vlookup function in the Excel Vlookup example:

+VLOOKUP(

The Vlookup arguments:

lookup_value

What are we looking for?

Reference the cell that contains the text or value:

=VLOOKUP(G15,

table_array

From which list are we doing a lookup on?

Place in the cell range of the Stock List:

+VLOOKUP(G15, $B$14:$D$17,

col_index_num

From which column do we want to retrieve the value?

We want to retrieve the Price which is the SECOND column from our table array:

+VLOOKUP(G15, $B$14:$D$17, 2,

[range_lookup]

Do we want an exact match?

Place in FALSE to signify that we want an exact match:

+VLOOKUP(G15, $B$14:$D$17, 2, FALSE)

About The Author

John Michaloudis

John Michaloudis is the Founder & Chief Inspirational Officer of MyExcelOnline!

You May Also Like

Leave a Comment Cancel reply

Can you provide details on the different types of error messages in Vlookup. I’ve been doing Vlookups a long time and I am totally stumped on an error message I’m getting (in particular #NA. Thank you

Bryan says:

November 7, 2019 at 4:58 PM

Hi Laura,

An #N/A error means it’s trying to look for something but it’s not there. You can check out this article: https://support.office.com/en-us/article/how-to-correct-a-n-a-error-a9708411-f82e-4e1b-8a7e-28c28311b993

Kind Regards,
Bryan

Is there any way i can put an entire list below one cell and once i click on that cell it drops down the entire list.

Bryan says:

November 4, 2018 at 12:28 PM

Hi Sruil,

I believe if you follow steps 1 and 2 in the tutorial, you should have the dropdown list on that cell.

Regards,
Bryan

Question… How can I make the formula so that if there is nothing to lookup for it just stays blank and doesnt show an “#n/a” on the cell.

My formula is
+vlookup($a20,Sheet1!c2:g2,2,False)

But where theres no input on a20 (which is a data validation) the cell shows that “#n/a”.

John Michaloudis says:

December 21, 2017 at 4:37 PM

Natalya,

You will need to insert an IFERROR formula before the VLOOKUP, as I explain here: https://www.myexcelonline.com/blog/iferror-introduction/

Cheers,
John

Need some assistance referencing data via drop downs. I am creating an ITIL Matrix for Services within a Business.

Table
Service, Assessment1, Assessment1Impact, Assessment1Urgency, Assessment1Prioriity, Assessment2Impact, Assessment2Urgency, Assessment2Prioriity.

User would select the Service via drop down (Which I have working through Data Validation drop down).
User would then select the Assessment (which there would be Availability Impact, Functionality etc).
I need the second drop down for Impact Assessment to be dependent on the Service field, then once Assessment is selected, display what the Impact, Urgency and Priority ratings would be for the user to determine if it is a Critical Service, High, Medium or Low.

Thanks for your help!

John Michaloudis says:

December 21, 2017 at 5:53 PM

Hey Rosie,

Does this tutorial help: https://www.myexcelonline.com/blog/indirect-function-for-dependent-dropdown-lists-in-excel/

Cheers,
John

In a few places you have +VLookUp( …
Is that a typo? I’m guessing it should be an =VLookUp(

John Michaloudis says:

October 27, 2017 at 12:00 AM

No it is not a typo. I have a European keyboard so the + sign is one click away, whereas the = sign is 2 clicks away.

You can start formulas with a + sign which many people dont know about!

Cheers,
John

This is my first lesson of your website.
I found vlookup function very useful.Specially the way you have demonstrated it.Thank You very much.I hope i will get informative lessons every week.

John Michaloudis says:

September 21, 2017 at 11:45 PM

Hey Kapil,

Im sure that with each weekly lesson your Excel skill will increase by 5%

Cheers,
John

Unfortunately with my current version of Excel 2016 when downloading file get message:
File format and extension of ‘Vlookup_Data-Validation2 (1).xls don’t match. The file could be corrupted or unsafe.
Ignore this message and file opens OK.
Is there something wrong with your file set up or mine?

John Michaloudis says:

August 11, 2017 at 11:01 PM

Hey Malcom,

The Excel file was saved as an .xls but it will still work with Excel 2016 and it is totally safe.

The message is a generic safeguard that Microsoft puts for older .xls files that get opened in Excel 2016.

Cheers,
John

Love the column. But, I’m trying to take it a step further. Not sure I can. I have a list of roughly 200 schools in a drop down box. Most schools have multiple counselors. I was hoping to create a second dropdown box that has a list of just the counselors for the specified school. Then use the vlookup to add in the subsequent info. Is there a way to use an array formula to put in the second dropdown box to accomplish that?

John Michaloudis says:

July 4, 2017 at 4:56 PM

Hey Brent,

Here is how you can add a second drop down which is dependent on the first drop down: https://www.myexcelonline.com/blog/indirect-function-for-dependent-dropdown-lists-in-excel/

Cheers,
John
Mike says:

March 12, 2019 at 10:52 AM

Hi John, I have exact the same question. The link is unfortionally not working. Is it replaced?

Hope to hear from you. Mike
Bryan says:

March 29, 2019 at 5:18 AM

Hi Mike,

Thanks for raising this up! Here’s the updated link: https://www.myexcelonline.com/blog/indirect-function-for-dependent-dropdown-lists-in-excel/

Kind Regards,
Bryan

Thank you sir !!
This saved my day

John Michaloudis says:

May 26, 2017 at 2:54 PM

Im glad you liked this great Excel vlookup example!

Keep om EXCELling!

Cheers,
John

I have a drop down list in cell A1 with 2 options, let’s say option A and B. Depending on which option I chose, a dependent list corresponding to each option would appear in cell B2. I want to know if it’s possible to use lookup in cell C2 for the two dependent list that appears in the same cell, B2 keeping in mind that the dependent list in that cell depends on the option i chose in cell A1. Thank you!

John Michaloudis says:

May 19, 2017 at 12:16 PM

Hey Anne,

Yes that is totally possible to do in Excel.

If you have a sample workbook, then send it over to me for me to check that it is correct.

Thanks,
John

This actually made a lot of sense! I do have a question though, trying to put together a quotation spreadsheet, with each company having a different tab. Is there a way of using look up to know which sheet to look in based on data from a drop down list?

John Michaloudis says:

February 23, 2017 at 3:47 PM

Hey Allie,

This is totally possible by setting up Named Ranges for your Table Arrays in each Sheet.

Here is how this is done in one sheet: https://www.myexcelonline.com/blog/excel-named-ranges-with-vlookup-formula-excel/

I have done a workbook for you with your answer and you can have a look at my VLOOKUP formula in cell I15

Excel Workbook With Answer

Cheers,
John

Is it possible to do a vlookup with two data validation list. So for example, one list to pick a sport and another list to pick a name. Then vlookups for how many goals, assists etc.. (the goals and assists are other workbooks, one workbook per sport).

I hope this makes sense!

John Michaloudis says:

February 15, 2017 at 7:14 PM

Hey Claire,

I have a tutorial on how to create a dependent data validation list: https://www.myexcelonline.com/blog/indirect-function-for-dependent-dropdown-lists-in-excel/

If that does not help, then email me your sample workbook at [email protected]

Thanks,
John

Beginner in excel.hope to learn more from you.

John Michaloudis says:

November 4, 2016 at 7:50 PM

Hey Santosh,

I am sure that if you apply what I teach you via the blog tutorials or the free weekly Excel lessons (as a subscriber), you will increase your Excel skills dramatically!

Let me know if you have any Excel questions, I am here to help 🙂

Thanks,
John

Thanks for the lesson today

John Michaloudis says:

November 4, 2016 at 6:08 PM

You are very welcome Nicholas 🙂

Cheers,
John

Thanks

John Michaloudis says:

August 2, 2016 at 3:23 PM

You are welcome!

John

Very Useful it was
Thanks for Sharing !!!!!!!!!!!!!

John Michaloudis says:

April 18, 2016 at 12:35 PM

I am glad that you liked this tutorial Abhishek 🙂

Feel free to share it with your friends & colleagues.

Thanks,

John

many thanks, exactly the example I was looking for to compile a pricing sheet. cheers!

John Michaloudis says:

March 31, 2016 at 12:28 AM

Hey Adrian,

Glad you liked it and this is great for pricing sheets! Just imagine how tough life was before you learned this trick 🙂

Cheers,
John