Ishrak Khan
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.
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.
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.
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.
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
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.)
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
- How to Insert a Drop-Down List in Google Sheets (2 Easy Ways)
- Create Drop Down List in Google Sheets from Another Sheet
- How to Edit Drop-Down List in Google Sheets
- Create Multiple Dependent Drop Down List in Google Sheets
- How to Use Data Validation in Google Sheets from Another Sheet
- Create Drop Down List for Multiple Selection in Google Sheets
- How to Create Conditional Drop Down List in Google Sheets
