VLOOKUP with Drop Down List in Excel

VLOOKUP function is generally used to look for a value in the leftmost column in a table and the function will return a value in the same row from the specified column. With the use of a drop-down list, the VLOOKUP function is more effective to use. In this article, you’ll get to learn how you create a drop-down list and use the VLOOKUP function later by assigning values from the list.

How to Create a Drop Down List and Use VLOOKUP: 4 Easy Steps

Step 1: Creating a Data Table

To use the VLOOKUP function with the drop-down lists, first of all, we need a dataset. In the following picture, a random dataset is present where the amounts of sales for some salespersons have been recorded based on months.

There’s another table at the bottom where the salesman and month names have to be chosen from the drop-down lists. So, in C15 and C16 cells, we have to assign the drop-down criteria for salesmen and months.

In the output Cell C17, we’ll insert the VLOOKUP function to extract the number of sales for a particular salesman in a particular month.

Step 2: Defining Range of Cells with a Name

Now let’s define the range of cells containing the names of salesmen. To do this, we have to follow two simple steps as stated below:

➤ Select the range of cells B5:B13 first.

➤ In the Name Box, situated at the top-left corner, give a name to the selected range of cells. In our example, we have defined the range of cells with the name: ‘Salesman’.

Step 3: Setting Up the Drop Down Lists

After defining the range of cells (C5:C13) with a name, we have to set up the drop-down lists for salesman and month names.

➤ Select Cell C15.

➤ Choose the Data Validation command from the Data Tools drop-down under the Data tab.

You’ll find a dialogue box named Data Validation.

➤ In the Allow box, select the List option.

➤ In the Source box, type:

=Salesman

Or, select the range of cells B5:B13.

➤ Press OK and you’ll have just made the first drop-down list for the salesmen.

Similarly, you have to create another drop-down list for the months.

➤ Select Cell C16 and reopen the Data Validation dialogue box.

➤ In the Allow box, choose the List option.

➤ In the Source box, select the range of cells (C4:E4) containing month names.

➤ Press OK.

Both drop-downs are now ready to display the assigned values.

Step 4: Using VLOOKUP with Drop Down Items

Now select the name of a salesman from the drop-down list in C15.

Select the month name from the drop-down in C16.

Finally, in the output Cell C17, type the following formula:

=VLOOKUP(C15,B5:E13,MATCH(C16,B4:E4,0),FALSE)

Press Enter and you’ll find the sales value of Antonio for the month of February at once.

In this formula, the MATCH function has been used to define the column number of the selected month.

From the drop-down lists, you can now alter any salesman or month name in C15 and C16 which will be assigned to the embedded formula in C17 and thus you’ll find the sales value for any salesman in any month with two simple clicks only.

Download Practice Workbook

You can download the Excel workbook that we’ve used to prepare this article.

VLOOKUP with Drop-Down List.xlsx

Concluding Words

I hope the steps mentioned above to create the drop-down lists and the use of the VLOOKUP function later will now help you to apply them in your Excel spreadsheets when necessary. If you have any questions or feedback, please let me know in the comment section.

<< Go Back to Advanced VLOOKUP | Excel VLOOKUP Function | Excel Functions | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!