How to Use VLOOKUP with Multiple Criteria

Let’s talk about how to use VLOOKUP with multiple criteria. After all, I often use this formula to find data based on more than one thing. Fortunately, there’s an easy way to do this.

How to Use VLOOKUP Based on Multiple Criteria

Let’s 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

Note that this guide focuses on Google Sheets. Here’s the guide on how to use VLOOKUP in Microsoft Excel. Of course, there are other ways to pull information. Check out the LOOKUP function. Finally, there’s a more robust way to find information in your spreadsheet. Check out my guide on using the query function based on multiple criteria.

Using a Helper Column to VLOOKUP Multiple Criteria in Google Sheets

My preferred way to to find a lookup value based on more than one criteria involves the use of an additional “Helper” column.

Here’s an example: I have a spreadsheet with department, area code, and bonus. If I want to use an index match formula, I just add a helper column right before the Department column. That way, it becomes the first column in the range.

Note that you can use XLOOKUP to save even more time. It’s a more advanced formula, and it often corrects this issue immediately.

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

Here are 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 C4 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 lookup 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.

The Basic VLOOKUP Function Lookup Criteria

If you’re just getting started, you’ll want to understand the basics of how to use VLOOKUP. 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. Note that this is a single lookup 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:

Key Takeaways and Clarifications

This guide explored how to use the VLOOKUP function with multiple criteria in Google Sheets. We examined two primary methods: using a helper column and utilizing the ARRAYFORMULA function to create a virtual table. By concatenating the criteria into a single string, VLOOKUP can effectively search for values based on multiple conditions.

The helper column method involves adding an extra column to the dataset, while the ARRAYFORMULA method constructs a dynamic virtual table within the formula. Both methods allow for efficient and accurate data retrieval based on multiple criteria, enhancing the functionality of VLOOKUP in complex scenarios. Reminder: I’m always happy to answer questions in the comments.

Here’s a quick look at the takeaways from this guide:

1. Understanding VLOOKUP: VLOOKUP is a powerful function used to search for and retrieve data from a specific column in a table based on a matching key value from another column.
2. Using Multiple Criteria: When needing to match multiple criteria, VLOOKUP alone is insufficient. Combining criteria into a single searchable string is essential for accurate data retrieval.
3. Helper Column Method:
   • Steps: Insert a new helper column, concatenate the criteria values, and adjust the VLOOKUP formula to search within the new column.
   • Advantages: Simple to implement and understand.
   • Considerations: Modifies the original table structure by adding an extra column.
4. ARRAYFORMULA Method:
   • Steps: Use ARRAYFORMULA to create a virtual table combining the criteria columns and target column, then apply VLOOKUP.
   • Advantages: No modification to the original table structure is needed.
   • Considerations: Slightly more complex to understand and implement, but offers a cleaner solution.
5. Formula Breakdown:
   • Helper Column: =VLOOKUP(B17&" "&C17, $A$4:$D$12, 4, FALSE)
   • ARRAYFORMULA: =ARRAYFORMULA(VLOOKUP(B17&" "&C17, {$A$4:$A$12&" "&$B$4:$B$12, $C$4:$C$12}, 2, FALSE))
6. Locking References: Use absolute references ($) in the formula to prevent shifting of the search range when copying the formula across multiple cells.
7. Visual Representation: Including screenshots or examples of the data and steps can significantly aid in understanding and applying these methods.
8. Alternative Methods: Consider exploring other functions like INDEX & MATCH, QUERY, or FILTER for complex lookups and scenarios where VLOOKUP may have limitations.

By applying these methods, users can leverage VLOOKUP’s powerful data retrieval capabilities even when dealing with multiple criteria, enhancing their data analysis and management in Google Sheets.