Have you ever wanted to use the VLOOKUP function with multiple criteria? For example, you might want to use a combination of first name and last name to search for a value using Vlookup.
In this article, you will learn how to search for multiple criteria with Vlookup in Google Sheets, with three different scenarios.
1. Searching for Multiple Criteria in a Single Column
In this case, we want to combine the search criteria to use in the Vlookup formula. For example, we have the first name and last name of a person, but the table we want to search only has a column for the full name.
The trick here is to nest a concatenation formula inside the Vlookup to combine the criteria before the search.
The formula for this Vlookup with multiple criteria is relatively simple:
=VLOOKUP(CONCATENATE(A2, " ", B2), A2:B9, 2, FALSE)
This is a classic Vlookup formula, with concatenated values as the first argument. We need to combine the first name and last name before searching the full name in the table with Vlookup.
This part of the formula:
CONCATENATE("Bob", " ", "Davis")
combines “Bob” in column A and “Davis” in column B into “Bob Davis,” which is the value we then search for in the table with Vlookup.
Want a copy of the example spreadsheet?
Yes, click here.
2. Searching for a Single Criterion in Multiple Columns with a Helper Column
This scenario is the reverse of the first one. In other words, we have a complete search term, but our lookup table has multiple columns to search.
For example, our search term is the full name of someone, but the lookup table has a column for first name and a column for last name. In this situation, we cannot perform a standard Vlookup search.
What we do is create a helper column in the lookup table, which combines the required columns to create a new search column. We thus combine the first name and last name to create a helper column containing the full name.
There are different concatenation formulas you could use to create a helper column, for example:
=CONCATENATE(A2, " ", B2)
The helper column is highlighted in yellow in the image above.
Then, it’s just a matter of using a standard Vlookup search using this helper column as the search column.
For example, the formula in the image above uses helper column C (yellow) as the search column:
=VLOOKUP(D2, A2:C9, 3, FALSE)
Want a copy of the example spreadsheet?
Yes, click here.
3. Searching for a Single Criterion in Multiple Columns Dynamically with Array Formulas
This is exactly the same scenario as number 2 above, but this time, instead of creating a helper column directly in the table, we will use the Array formula to create everything dynamically on the fly.
Let’s break this down into steps:
3.1 Create an Array of Criterion Columns
First, we need to generate the array of full names using this formula:
=ArrayFormula($A$2:$A$9&" "&$B$2:$B$9)
(This is an Array formula. You enter the ranges $A$2:$A$9&” “&$B$2:$B$9, then press Ctrl + Shift + Enter, or Cmd + Shift + Enter (Mac) to add the Array formula designation.)
Here is the result, generated by the formula in cell B15:
3.2 Add the Other Columns from the Original Table
At this step, we build the new lookup table by adding the other columns from the original table that we want to search, using this formula:
=ArrayFormula({$A$2:$A$9, $D$2:$D$9, $E$2:$E$9})
Here, we used literal arrays – the curly braces { … } – to combine the arrays. By using a comma between the arrays in curly braces, they are treated as columns side by side, which is what we want in this case, as you can see in the image:
(Note 1: If we wanted to stack the arrays on top of each other, we would use the notation “;”.)
(Note 2: For most European users, your syntax is a bit different. Read this article: Explanation of syntax differences in your formulas due to the location of your Google Sheets.)
3.3 Perform the Vlookup Search
Now that we have created a new table with the full name column, we just need to use it as the range input in a standard Vlookup search, as shown in the first image of section 3 above.
The formula for this is:
=VLOOKUP($F$2, $G$2:$I$9, 3, FALSE)
Note that the array formula envelope stays outside the entire formula.
Want a copy of the example spreadsheet?
Yes, click here.
