How to Use the VLOOKUP Function for Multiple Criteria in Google Sheets (Easy Steps)

Do you find yourself constantly searching for data across different sheets or tables? It can be a tedious task to manually look up information. But fear not, Google Sheets has a solution for you—the VLOOKUP function.

By using the VLOOKUP function, you can instruct Google Sheets to automatically search for values and retrieve corresponding data from another table. This table can be on the same sheet or a different one.

The only problem is that the syntax of the VLOOKUP function only allows you to search for one column at a time.

So, what do you do when you have multiple criteria or want to check multiple interdependent columns at once? You work around this limitation by combining the VLOOKUP function with other Google Sheets functions, such as IMPORTRANGE.

Syntax of a Regular VLOOKUP Function

Before we dive into using the VLOOKUP function for multiple criteria, let’s first understand the syntax of a regular VLOOKUP function:

=VLOOKUP(search_criterion, range, index, [sorted])

Here,

• search_criterion is the key value you want to search for in another table. It can be a value or a reference to a cell containing the value.
• range is the range of cells (in the source table) where you want the VLOOKUP function to search for the search_criterion. Ensure that this range contains the column with the search_criterion as the first column. Also, make sure it includes the column with the target value you want to retrieve.
• index is the column number in the range that contains the target value you want to retrieve. Note that the first column in the range has an index of 1, the second column has an index of 2, and so on.
• sorted is an optional parameter. It can be either TRUE or FALSE. It indicates whether the search column should be sorted or not.

For example, in the image below, we have two tables. One contains personal information about employees, and the other contains their total sales for a given month.

Both tables have a common column—the employee ID—which can be considered as a key or unique identifier in both tables.

To display the hourly rate of employee “E010,” you need to retrieve it from the employee table using the VLOOKUP function. Here’s what you would enter in cell F3:

=VLOOKUP(A3, $A$3:$C$8, 3, false)

You will then get the following result:

When Do We Need to Search for Multiple Criteria in Google Sheets?

The previous case only involved searching for a single criterion—a match for the employee ID. However, most of the time, the criterion is more complex than that.

There may be several reasons why multiple criteria are needed for the VLOOKUP function. Here are a few examples:

1. You may have separate columns for the first name and last name in the source table. Therefore, you need to search both columns to retrieve a corresponding value, as shown below:

2. You may need to check the satisfaction of 2 or more conditions to retrieve a value. For example, you might need the sum of scores only for students who passed a test and studied French, as shown below:

3. You may need to search a separate table to find an employee’s salary in a certain department with a specific area code.

There are many other similar situations to the examples above. But you get the general idea.

You might argue that using a FILTER would be a simpler option for the above situations. However, FILTER cannot retrieve data from a different sheet.

Using IF functions would also be too complex as you would then have to deal with nested IF functions (and nobody likes that!).

How to Search for Multiple Criteria in Google Sheets

Let’s take the following example, where you have a table containing the bonus corresponding to each department and area code:

In Table 2, we need to search for the bonus corresponding to a specific department in a particular area code and display the retrieved value in the Bonus column (column E).

There are two ways to achieve this using the VLOOKUP function:

1. Using an Auxiliary Column
2. Using the ARRAYFORMULA function

You can learn more here

Using an Auxiliary Column to Search for Multiple Criteria in Google Sheets

The first method involves using an additional auxiliary column, which will contain a combination of the criterion cells.

In our example, we can insert the auxiliary column right before the department column, so it can be the first column in the search range.

The auxiliary column can contain a combination of the department and area code for each row, separated by a space.

Let’s look at the steps to create and use this auxiliary column with VLOOKUP to get the appropriate bonus value for Table 2:

1. Insert a new column just before the first column of Table 1. This column will act as our auxiliary column. To do this, right-click on the header of the first column (column A) and select the “Insert 1 left” option from the context menu that appears.

2. Select the first cell of the newly created column (cell A4) and type the formula: =B4&" "&C4.

3. Press Enter. Cell A4 should now contain the content of cell B4 followed by a space and the content of cell C5.

4. Double-click the fill handle of cell A4 to copy the formula to the rest of the cells in the A column. The A column should now look like this:

5. Now that the auxiliary column is ready, we can use the VLOOKUP function. You’ll notice that adding a new column has shifted the contents of Table 2 by one cell to the right. If desired, you can select the contents and move them one cell to the left.

6. Next, select cell E17 (Bonus column in Table 2) and enter the formula: =VLOOKUP(B17&" "&C17, $A$4:$D$12, 4, false)

7. Press Enter.

8. You should now see the bonus value corresponding to “Operations BH12” from Table 1.

9. Double-click the fill handle of cell E17 to copy the formula to the rest of the cells in column A.

10. You should now see all the bonus values corresponding to each department and area code in Table 2.

Note: When typing the VLOOKUP formula, don’t forget to lock the references of the second parameter by pressing the F4 key. This will ensure that the search range does not shift when the formula is copied to the rest of the cells.

Explanation of the Formula

Let’s break down the VLOOKUP formula to understand each parameter used:

=VLOOKUP(B17&" "&C17, $A$4:$D$12, 4, false)

• B17&" "&C17: The search value we used here is a combination of the department and area code that we want to search, separated by a space (which is exactly the format we used in our auxiliary column).
• $A$4:$D$12: We know that our search range should always have the search column as the first column. The search range now starts from A4 to D12 because A4 is the first cell of our auxiliary column.
• 4: Due to the addition of an extra column to the left, our target column has now shifted by one cell to the right. Therefore, the bonus column is now at index 4 in our search range.
• false: A value of FALSE for this parameter indicates that the first column of the search range does not need to be sorted in ascending order.

Since the search criterion and the auxiliary column are in the exact same format (department followed by a space, followed by the area code), the VLOOKUP function can easily search for the corresponding bonus and return it.

Using the ARRAYFORMULA Function to Search for Multiple Criteria in Google Sheets

This method does more or less the same as the first method. The only difference is that this time, the auxiliary column is created dynamically, rather than having to physically create an additional column for it.

The method uses the ARRAYFORMULA function to create a ‘virtual’ table containing the following columns:

• A column containing a combination of the criterion cells.
• The target column from the search range.

Let’s apply this to our case. Here are the steps to create and use the ARRAYFORMULA function with VLOOKUP to get the appropriate bonus value for Table 2:

1. Select cell E17 (Bonus column in Table 2) and enter the formula: =ARRAYFORMULA(VLOOKUP(B17&" "&C17, {$A$4:$A$12&" "&$B$4:$B$12, $C$4:$C$12}, 2, false))

2. Press Enter.

3. You should now see the bonus value corresponding to the department: Operations and the area code: BH12 from Table 1.

4. Double-click the fill handle of cell E17 to copy the formula to the rest of the cells in column A.

5. You should now see all the bonus values corresponding to each department and area code in Table 2.

Note: When typing the VLOOKUP formula, don’t forget to lock the references of the second parameter by pressing the F4 key. This will ensure that the search range does not shift when the formula is copied to the rest of the cells.

Explanation of the Formula

Let’s break down the formula we used to understand what exactly happened:

=ARRAYFORMULA(VLOOKUP(B17&" "&C17, {$A$4:$A$12&" "&$B$4:$B$12, $C$4:$D$12}, 2, false))

• First, we use ARRAYFORMULA to create a sort of virtual table. This virtual table should contain the following columns:
  • A column containing a combination of the values from cells $A$4:$A$12 and $B$4:$B$12, separated by a space between each cell value: {$A$2:$A$9&" "&$B$2:$B$9}
  • A column containing the values from the Bonus column of Table 1: $C$2:$D$9
• This is specified between curly braces because we want to return an array or virtual table of cells: { $A$2:$A$9&" "&$B$2:$B$9, $C$2:$D$9 }
• Note that we used a comma as a separator for the array formula because we wanted to treat the parts as columns next to each other, as shown below:
  • If we wanted them to be rows on top of each other, we would have used a semicolon instead of a comma.
• Next, we apply the VLOOKUP function using the above array formula as the second parameter. The rest of the parameters are the same as before:
  • search_criterion: The search key is again a combination of the department and area code that we want to search, separated by a space.
  • range: The range is now the array or virtual table: {$A$2:$A$9&" "&$B$2:$B$9, $C$2:$D$9}
  • index: Since the virtual table created only has two columns, where the Bonus is the second column, we use 2 as the index value.
  • sorted: A value of FALSE for this parameter indicates that the first column of the search range does not need to be sorted in ascending order.

Since the search criterion and the first column of the returned table in the range are in the exact same format (department followed by a space, followed by the area code), the VLOOKUP function can easily search for the corresponding bonus and return it.

This was just a brief overview of two ways to use the VLOOKUP function when multiple criteria need to be considered.

We encourage you to try out the examples we covered in this tutorial yourself. It will help you understand how the formulas work and give you a better understanding.

We hope you found this tutorial helpful!