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.

## 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 E3:

=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:

- 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: - 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: - 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

### 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:

- 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. - Select the first cell of the newly created column (cell A4) and type the formula: =B4&” “&C4.
- Press the
*Return*key. The cell A4 should now contain the contents of cell**B4**and**C5**together, separated by only space. - 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:
- 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.
- Next, select cell E17 (Bonus Column of Table 2) and type the formula: =VLOOKUP(B17&” “&7,$A$4:$D$12,4, false)
- Press the return key.
- You should now see the bonus value corresponding to “
*Operations BH12*” from*Table 1*. - Double click on the fill handle of cell E17 to copy the formula to the rest of the cells in column A.
- 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)

The search key we used here is a combination of**search_key**:*Department*and Area code values we want to lookup, separated by a space (which is exactly the format we used in our*Helper*column).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.**range**:Due to the addition of an additional column to the left, our target column has now shifted one cell to the right. Therefore, the**index**:*Bonus*column is now at index 4 of our search range.: A FALSE value for this parameter indicates that the first column of the search range does not need to be sorted in ascending order.*is*_sorted

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*:

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

- Press the return key.
- You should now see the
*Bonus*value corresponding to*Department: Operations*and*Area Code:BH12*from*Table 1*. - Double click on the fill handle of cell
**E17**to copy the formula to the rest of the cells in column A. - 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

- A column containing a combination of values from cells
- 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:**