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.

A Sample of Practice Spreadsheet

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

navigation of data validation in order to use vlookup drop down list in google sheet

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.

setting parameters for data validation criteria

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

drop down menu for vlookup function

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.

vlookup function structure

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


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.

detail understanding of the arguments of vlookup in google sheets

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.

Read More: How to Edit Data Validation in Google Sheets (With Easy Steps)

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.

drop down list feature for row values

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


Advance vlookup function usage

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.

final output of advance vlookup drop down list in google sheets

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

Read More: How to Create Dependent Drop Down List in Google Sheets

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.

Related Articles

Ishrak Khan

Ishrak Khan

Ishrak Khan is a writer and urban planner born in Dhaka, Bangladesh, one of the largest mega-cities. He is a professional Content Developer closely working with OfficeWheel. He simply likes to enthrall his readers. He loves Football, Nico Robin, Deserts, and Twix. If you like the post, give him a heart on Instagram.

We will be happy to hear your thoughts

Leave a reply