How to VLOOKUP Multiple Criteria in Google Sheets (Easy Steps)

When you have to refer to data from multiple sheets or tables, physically looking them up can be painstaking. Google Sheets helps overcome this problem with its VLOOKUP function.

Using the VLOOKUP, or Vertical Lookup function, you can get Google Sheets to automatically lookup values and retrieve matching data from another table. This table could be on the same sheet or a different sheet.

The only problem is that the syntax of the VLOOKUP function only lets you look up one column at a time.

So if you have multiple criteria or want to check multiple interrelated columns at the same time, you have to find a way around this limitation.

This can be done by pairing the VLOOKUP function with other Google Sheets functions, even the IMPORTRANGE function..

Syntax of a Regular VLOOKUP Function

In general, a VLOOKUP function has the following syntax:

=VLOOKUP(search_key, range, index, [is_sorted])

Here,

• search_key is the key value that you want to look up from another table. This can either be a value or a reference to a cell containing the value.
• range is the range of cells (in the source table) within which you want the VLOOKUP function to look for the search_key . Remember to ensure that this range contains the column with the search_key as its first column. Also, make sure that it contains the column with your target value (that you want to retrieve).
• index is the column number within range containing the target value that you want to retrieve. Note that the first column within the range has an index of 1, the second column has an index of 2, and so on.
• is_sorted is an optional parameter. This can either be TRUE or FALSE. It indicates if the search column needs to be sorted or not.

For example, in the following image, we have two tables. One that contains personal information about employees and another that contains their total sales on a particular month.

Both tables have a common column, the Employee ID, so this could be considered as a key-value or a unique identifier in both tables.

To display the hourly rate of, say, Employee ID “E010”, you need to retrieve it from the Employee table using the VLOOKUP function. This is what you will enter in cell F3:

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

So that you get the following result:

When do we need to VLOOKUP Multiple Criteria in Google Sheets?

The above case only involved looking up a single criterion, a match for the Employee ID. However, most of the time the criterion is more complex than that.

There may be a number of reasons for VLOOKUP’s multiple criteria. Here are some examples:

1. You might have separate columns for first and last names in the source table. So you would need to lookup both columns to retrieve a corresponding value, as shown below:
2. You might need to check for the satisfaction of 2 or more conditions to retrieve a value. For example, you might need the sum of scores of only those students who passed a test and studied French, as shown below:
3. You might need to look up a separate table to find the Salary of an employee from a certain department in a certain Area code.

There are numerous other situations like the ones listed above. But you get the gist.

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

Using IF functions will also be overkill because you will then need to deal with multiple nested IFs(and nobody likes that!).

How to VLOOKUP Multiple Criteria in Google Sheets

Let us take the following example, where you have one table containing the Bonus corresponding to each Department and Area code.

In table 2, we need to look up the Bonus corresponding to a particular Department in a particular Area code and display the retrieved value in the Bonus column (column E).

There are two ways to accomplish this using the VLOOKUP Function:

• Using a Helper Column
• Using the ARRAYFORMULA Function

Read more: Google Sheets LOOKUP

Using a Helper Column to VLOOKUP Multiple Criteria in Google Sheets

The first method involves the use of an additional “Helper” column, which will contain a combination of the cells in the criteria.

In our example, we can insert the Helper column right before the Department column, so that it can be the first column of the search range.

The Helper column can contain a combination of the Department and Area code for each row, separated by a space.

Let us see the steps to create and use this helper column with VLOOKUP to get the appropriate Bonus value for Table 2:

1. Insert a new column right before the first column of Table 1. This will act as our Helper column. For this, you need to right-click on the column header of the first column (column A) and select ‘Insert 1 left’ 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 the Return key. The cell A4 should now contain the contents of cell B4 and C5 together, separated by only space.
4. Double click on the fill handle of cell A4 to copy the formula to the rest of the cells in column A. Here’s what the column A should look like at this point:
5. Now that the helper column is ready, we can go ahead and use the VLOOKUP function. You will notice that the addition of a new column has shifted the contents of table 2 one cell to the right. If you want, you can select the contents and move them back to one cell to the left.
6. Next, select cell E17 (Bonus Column of Table 2) and type the formula: =VLOOKUP(B17&” “&C17,$A$4:$D$12,4, false)
7. Press the return key.
8. You should now see the bonus value corresponding to “Operations BH12” from Table 1.
9. Double click on 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 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 in 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 below to understand each parameter used:

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

• search_key: The search key we used here is a combination of Department and Area code values we want to lookup, separated by a space (which is exactly the format we used in our Helper column).
• range: We know that our search range should always have the lookup column as its first column. The search range now starts from A4 to D12, since A4 is the first cell of our helper column.
• index: Due to the addition of an additional column to the left, our target column has now shifted one cell to the right. Therefore, the Bonus column is now at index 4 of our search range.
• is_sorted: A FALSE value for this parameter indicates that the first column of the search range does not need to be sorted in ascending order.

Since both the search_key and Helper column are in exactly the same format (having Department followed by space, followed by Area code), the VLOOKUP function can easily look up the corresponding Bonus and return it.

Using the ARRAYFORMULA Function to VLOOKUP Multiple Criteria in Google Sheets

This method does more or less the same thing as the first method. The only difference is that this time the helper column is dynamically created, instead of having to physically create an extra column for it.

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

• A column that holds a combination of cells in the criteria
• The target column from the search range

Let us 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 of Table 2) and type the formula:

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

2. Press the return key.
3. You should now see the Bonus value corresponding to Department: Operations and Area Code:BH12 from Table 1.
4. Double click on 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 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 in 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 that we used to understand what exactly happened:

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

• First, we use ARRAYFORMULA to create a sort of virtual table. This virtual table is to contain the following columns:
  • A column containing a combination of values from cells $A$4:$A$12 and cells $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$4:$C$12
• This is specified within curly braces because we want to return an array or a virtual table of cells: =ArrayFormula({$A$2:$A$9&” “&$B$2:$B$9,$C$2:$D$9})

Note that we used the “,” as the separator for the array formula because we want 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 its second parameter. The rest of the parameters are the same as before:
  • search_key: The search key again is a combination of Department and Area code values we want to lookup, 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 consists of just two columns, where Bonus is the second column, we use 2 as the index value.
  • is_sorted: A FALSE value for this parameter indicates that the first column of the search range does not need to be sorted in ascending order.

Since both the search_key and first column of the array returned in the range are in exactly the same format (having Department followed by space, followed by Area code), the VLOOKUP function can easily look up the corresponding Bonus and return it.

This was just a brief overlook of two ways in which we can use the VLOOKUP function when there are multiple criteria to be considered.

We encourage you to try out the examples we discussed in this tutorial by yourself. This will help you get a feel of how the formulas work and will give you a deeper understanding of it.

I hope you found this tutorial useful!

Other Google Sheets tutorials you may like:

• How to VLOOKUP from Another Sheet in Google Sheets
• How to Use the INDIRECT function in Google Sheets
• How to Compare Two Columns in Google Sheets (for Matches & Differences)
• How to Combine Cells In Google Sheets
• How to Get the Last Value in a Column in Google Sheets (Lookup Formula)
• How to Use SUMIFS Function in Google Sheets