How to Create a Drop Down List and Use VLOOKUP in Excel (4 Easy Steps)

Step 1: Creating a Data Table

To use the VLOOKUP function with the drop-down lists, we need a dataset. The following picture shows a random dataset, with the sales amounts for some salespersons recorded based on months.

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

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

Step 2: Defining the Range of Cells with a Name

Steps:

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

Step 3: Setting Up the Drop Down Lists

Steps:

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.

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.