Nehad Ulfat
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.
Read More: Use VLOOKUP with Multiple Criteria in Excel (6 Methods + Alternatives)
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’.
Read More: VLOOKUP Partial Text from a Single Cell in Excel
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.
Similar Readings
- VLOOKUP Not Working (8 Reasons & Solutions)
- Why VLOOKUP Returns #N/A When Match Exists? (5 Causes & Solutions)
- How to Vlookup and Sum Across Multiple Sheets in Excel (2 Formulas)
- Excel VLOOKUP to Find Last Value in Column (with Alternatives)
- How to VLOOKUP with Multiple Conditions in Excel (2 Methods)
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.
Read More: INDEX MATCH vs VLOOKUP Function (9 Examples)
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.
