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



How to Combine VLOOKUP and IFERROR to Replace the #N/A Error in Excel


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

Excel´s EndOfMonth function   What does it do? Returns the last day of the month after a start date Formula breakdown: =EOM(start_date, months) What it means: =EOM(Your Start Date, enter 0 for current end of month, 1 for the next end of month, and so on...) The EOMONTH (EndO...
GETPIVOTDATA Function What does it do? A formula that extracts data stored in a Pivot Table Formula breakdown: =GETPIVOTDATA(data_field, pivot_table, , ,...) What it means: =GETPIVOTDATA(return me this value from the Values Area, any cell within the Pivot Table, ,...)   T...
Advanced SUMPRODUCT Function: Sum Multiple Criteri... What does it do? It returns the sum of multiple criteria from the corresponding ranges or arrays Formula breakdown: =SUMPRODUCT((array 1 criteria) * (array2 criteria) * array values) What it means: =SUMPRODUCT((find my criteria in this array) * (find my criteri...
Consolidate with 3D Formulas in Excel 3D Formulas or References in Excel are a great way to consolidate data from multiple sheets. 3D Formulas reference several worksheets that have the same structure which allows you to consolidate by using the SUM function. Formula breakdown: SUM(Sheet1:Sheet4!A1) ...