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

See also  Autosum an Array of Data in Excel

DOWNLOAD EXCEL WORKBOOK

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

STEP 1: Go to Data > Data Validation. 

VLOOKUP Example: Vlookup with a Drop Down List

 

STEP 2: Select List in the Allow dropdown

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

VLOOKUP Example: Vlookup with a Drop Down List

Your dropdown is ready.

VLOOKUP Example: Vlookup with a Drop Down List

 

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

+VLOOKUP(

VLOOKUP Example: Vlookup with a Drop Down List

The Vlookup arguments:

lookup_value

What are we looking for?

Reference the cell that contains the text or value:

=VLOOKUP(G15,

VLOOKUP Example: Vlookup with a Drop Down List

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,

VLOOKUP Example: Vlookup with a Drop Down List

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,

VLOOKUP Example: Vlookup with a Drop Down List

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

VLOOKUP Example: Vlookup with a Drop Down List

 

See also  TODAY Formula in Excel

The price now dynamically changes based on your selection:

VLOOKUP Example: Vlookup with a Drop Down List

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

Further Learning:

 

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

Pinterest

Share on Facebook

Facebook

Tweet about this on Twitter

Twitter

Share on LinkedIn

Linkedin