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

WEEKDAY function: Introduction The WEEKDAY function returns the day of the week corresponding to a date.  The day is given as an integer, ranging from 1 (Sunday) to 7 (Saturday).So if you want to find out on what day you were born, then the WEEKDAY function will remind you.DOWNLOAD WORKBOOKHelp...
INDEX Function Introduction The INDEX function in Excel returns a cell´s values from within a table/array.It works like a map, so you have to select a range (table/array) and tell it to return you the coordinates (Row & Column numbers).So if you want to return values from a Price List or large d...
SUMIFS Function: Introduction The SUMIFS function allows you to Sum multiple criteria.For example, you can select one Sales Rep from a list of Sales Reps and select one Region from a list of Regions and return the Sum of those arguments from a Sales list.  See how easy it is...DOWNLOAD WORKBOOK...
Getting the length of text with Excel’s LEN ... What does it do?Gives you the number of characters of the textFormula breakdown:=LEN(text)What it means:=LEN(text that you want to get the number of characters)There are times when you need to get the number of characters within a cell in Excel.  Thank...