How to Use VLOOKUP with Drop Down List in Google Sheets

Using the VLOOKUP function with drop down List in Google Sheets is an extremely streamlined, useful, and dynamic search and query tool. In simple terms, the function VLOOKUP in Google Sheets generally performs a vertical lookup. It looks for matching data, either from another table on the same sheet or from a different sheet, and retrieves it. Here, we’ll look at a few scenarios for using VLOOKUP for drop-down lists in Google Sheets.

Table of Contents

A Sample of Practice Spreadsheet

VLOOKUP Drop Down List

2 Scenarios of Using VLOOKUP with Drop Down List in Google Sheets

1. Basic Scenario to Drop Down List in Google Sheets

Consider that we have a dataset of sales and revenue of a super shop. We need to find, for instance, how many potatoes were sold and what the revenue was. Given that, It can be quickly sorted using the VLOOKUP Drop Down List feature.

Step 1: Navigate Data Validation from Data Tab

First of all, we are going to create a drop down menu in cell B19 below the table to navigate the product list. For this purpose, we are going to use the tool Data validation.

Data > Data Validation

Following the Data validation pop-up window, you can set the parameters. Here for this example, we are going to select the Criteria list from a range which is the product column.

Don’t forget to check the box next to “Show Drop-Down list in cell” and find something like that in the image below.

Now we have the drop-down list for the product column so that we can look for the information on any specific product.

Step 2: Include the VLOOKUP function.

To break down the steps simply, Input this formula to extract information on the products.

For this occasion only, The formula is described for a better understanding of the arguments.

=VLOOKUP(B22,$B$6:$E$15,2,0)

We are seeking two pieces of data side by side for any given product, such as Sold (Unit) and Revenue. In both situations, we must employ the VLOOKUP formula.

The key to effectively using the VLOOKUP function is to understand the index or column number.

Step 3: Navigate Drop Down Options

Now, in brief, the user may now look for any information regarding the product just by switching the option naturally whether it is Palm Oil or Eggs.

2. The Advanced Scenario to Drop Down List in Google Sheets

We previously listed the Column value in a Drop Down menu. If we include a Drop Down list with the Row values as well, we may make it more straightforward and effective overall.

Step 1: Create a Drop Down List

Firstly, we have to choose the Data Validation criterion. Here are the steps.

Select the cell, here C18.
Data > Data Validation
Select Criteria from $B$5 to $E$5.

Usually, the output will be like the image above.

Step 2: VLOOKUP with MATCH Function

It should be noted that the formula automatically selects the appropriate column when a user modifies the criteria. As a result, it increases the flexibility of VLOOKUP use. To handle the logical argument, we must use the MATCH function.

Here is the formula

=VLOOKUP(B19,$B$6:$E$15,MATCH(C18,$B$5:$E$5,0),0)

The MATCH function assists in locating the pertinent column to return the lookup value. To be precise, it takes the value C18 and searches within the range $B$5:$E$5, returning 0 for a perfect match.

Therefore, the algorithm would return 2 if you wanted to know the Sold Unit because it is in column 2. (In the array range, it is the Second Cell.)

Final Words

Now that you know how to use the VLOOKUP function with the Drop-Down list, try to use it whenever you have a situation to fetch a value from a matching dataset.

Please feel free to post any questions or suggestions in the comments area below.