Last Updated on October 31, 2023 by Jake Sheridan
In this tutorial, you will learn how to VLOOKUP multiple criteria in Google Sheets.
How to VLOOKUP Multiple Criteria in Google Sheets
The VLOOKUP function in Google Sheets is a powerful tool that allows you to search for a value in a table and return a related value from another column.
Sometimes, however, you may need to search for multiple criteria in order to find the value you are looking for. For example, you may want to output the contact number of an employee given both the last name of the employee as well as their respective department.
In this guide, we will show you how to use the VLOOKUP function to search for multiple criteria in Google Sheets.
How to Use the VLOOKUP Function with Multiple Criteria in Google Sheets
Here’s how to use the VLOOKUP function with multiple criteria in Google Sheets.
Insert a new column to act as a helper column for the VLOOKUP formula.
Since we’ll be using VLOOKUP, it is most convenient to place this helper column on the leftmost side of the table.
We’ll need to populate this helper column with the two criteria we want to use. We can concatenate the values using the “&” symbol.
For example, if we want to use the criteria last_name and department, we can use the formula =B2&F2 to generate a new value for the helper column.
We’ll use the AutoFill feature to automatically fill out the rest of the helper column.
Next, we’ll set up the VLOOKUP formula to use a concatenation of the two criterias as the search key.
In the example above, we’ll prompt the user to place the desired last name and department to search for in cells I2 and I3. We’ll use the formula I2&I3 to create the search key we’ll use for our lookup table.
The user can now indicate the values for the two criteria. VLOOKUP will return the desired field if a match is found in the helper column.
This guide should be everything you need to VLOOKUP multiple criteria in Google Sheets.
You may make a copy of this example spreadsheet to test it out on your own.