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])


Excel VLOOKUP with Drop Down

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 VLOOKUP value also changes.

Click to learn how to insert a drop down menu with Data Validation

See how easy it is to apply this with a quick VLOOKUP example below.




STEP 1: Go to Data > Data Validation. 

Excel VLOOKUP with Drop Down


STEP 2: Select List in the Allow dropdown

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

Excel VLOOKUP with Drop Down

Your dropdown is ready.

Excel VLOOKUP with Drop Down


STEP 3: We need to enter the Vlookup function:


Excel VLOOKUP with Drop Down

The Vlookup arguments:


What are we looking for?

Reference the cell that contains the text or value:


Excel VLOOKUP with Drop Down


From which list are we doing a lookup on?

Place in the cell range of the Stock List:

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

Excel VLOOKUP with Drop Down


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,

Excel VLOOKUP with Drop Down


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)

Excel VLOOKUP with Drop Down


The price now dynamically changes based on your selection:

Excel VLOOKUP with Drop Down


Excel VLOOKUP with Drop Down


Helpful Resource:

Excel VLOOKUP with Drop Down

If you like this Excel tip, please share itEmail this to someone


Pin on Pinterest


Share on Facebook


Tweet about this on Twitter


Share on LinkedIn


Share on Google+


Related Posts

VLOOKUP with Multiple Criteria in Excel ‘VLOOKUP’ is now so common that everyone who has ever been working with Excel has heard of it. Not necessarily because they know how to use it, but because some savvy Excel-user always talks about it at the office. Advanced users have seen the use of adding more than one...
Sum a Range Using the INDEX Function You can sum a range of values within a table using the INDEX function in Excel.  This is valuable when you want to extract key metrics from a table and put them in an Excel Dashboard. To make this work you firstly need to start your Excel formula with the SUM function foll...
Cleaning Data with Excel’s TRIM Formula What does it do? Removes unneeded spaces in your text, except single spaces in between words Formula breakdown: =TRIM(text) What it means: =TRIM(text that you want extra spaces to be removed) In the quest for cleaner data, one of the common scenarios is remo...
Excel Subtotal Function – Include Hidden Val... What does it do? It returns a Subtotal in a list or database Formula breakdown: =SUBTOTAL(function_num, ref1) What it means: =SUBTOTAL(function number 1-11 includes manually-hidden rows & 101-111 excludes them, your list or range of data) ***Go to the bottom...