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.


Download Practice Workbook

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


How to Create a Drop Down List and Use VLOOKUP

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.

And 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.

vlookup with drop down list - creating dataset


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’.

vlookup with drop down list - defining range of cells with a name


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.

vlookup with drop down list - Setting Up the Drop Down Lists

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.

vlookup with drop down list - Setting Up the Drop Down Lists

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.

vlookup with drop down list - Setting Up the Drop Down Lists


Step 4: Using VLOOKUP with Drop Down Items

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

Using VLOOKUP with Drop Down Items in Excel

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

Using VLOOKUP with Drop Down Items in Excel

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.

Using VLOOKUP with Drop Down Items in Excel

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.


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. Or you can check out our other articles related to Excel functions on this website.


You May Also Like to Explore

Range Lookup with VLOOKUP in Excel (5 Examples)

10 Best Practices with VLOOKUP in Excel

VLOOKUP to Return Multiple Values Horizontally in Excel

How to Use VLOOKUP If Cell Contains a Word within Text in Excel

VLOOKUP Fuzzy Match in Excel

Nehad Ulfat

Hello, Welcome to my profile. I'm a Technical Content Creator as well as a Naval Architect & Marine Engineer. I have preferences to do analytical reasoning & writing articles on various statistical data. Here, you'll find my published articles on Microsoft Excel & other topics related to my interests!

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo