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.

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

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

DOWNLOAD EXCEL WORKBOOK

 

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 in the Excel Vlookup example:

+VLOOKUP(

Excel VLOOKUP with Drop Down

The Vlookup arguments:

lookup_value

What are we looking for?

Reference the cell that contains the text or value:

=VLOOKUP(G15,

Excel VLOOKUP with Drop Down

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,

Excel VLOOKUP with Drop Down

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,

Excel VLOOKUP with Drop Down

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

Excel VLOOKUP with Drop Down

 

The price now dynamically changes based on your selection:

excel dependent drop down list vlookup

This is how to use Vlookup with a Drop Down List!

 

HELPFUL RESOURCES:

JOIN OUR FREE EXCEL COURSES BELOW
Click here to access these FREE Excel courses!

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

email

Pin on Pinterest

Share on Facebook

Tweet about this on Twitter

Share on LinkedIn