Vlookup Multiple Criteria in Google Sheets

Have you ever needed to use Vlookup with multiple criteria?

For example, you may want to use first name and last name combined to search for a value using Vlookup.

In this post, you’ll see how to Vlookup multiple criteria in Google Sheets, with three different scenarios.

1. Vlookup Multiple Criteria into Single Column

In this case, we want to combine search criteria to use in the Vlookup formula. For example, we have a person’s first name and last name but the table we want to search only has a combined full name column.

The trick here is to nest a concatenation formula inside the Vlookup to combine the criteria prior to searching.

Vlookup Multiple criteria in Google Sheets

The formula for this Vlookup with multiple criteria is relatively straightforward:

=VLOOKUP(A15&" "&B15,$A$1:$C$9,3,false)

It’s a regular Vlookup formula, with concatenated values as the first argument. We need to combine first name and last name before searching for the full name in the table.

This part of the formula:

A15&" "&B15

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 worksheet?

Yes, click here.

2. Vlookup Single Criteria into Multiple Columns with Helper Column

This scenario is the opposite way round to the first one. In other words we have a complete search term, but our search table has multiple columns that need to be searched.

For example, our search term is the full name of someone, but the search table has a column for first name and a column for last name. In this situation, we can’t perform a standard vlookup.

What we do is create a helper column in the search table, which combines the required columns to create a new search column. So we combine the first and last names to create a helper column containing the full name.

Vlookup into multiple columns in Google Sheets

There are various concatenation formulas you could use to create a helper column, for example:

A2&" "&B2

=JOIN(" ",A2:B2)

=CONCATENATE(A2," ",B2)

The helper column is shown in yellow in the image above.

Then it’s simply a standard Vlookup using this helper column as the search column.

For example, the formula in the image above uses the helper column C (yellow) as the search column:

=VLOOKUP(A15,$C$1:$E$9,3,false)

Want a copy of the example worksheet?

Yes, click here.

3. Vlookup Single Criteria into Multiple Columns Dynamically with Array Formulas

This is exactly the same scenario as 2 above, but this time instead of creating a helper column directly in the table, we’ll use the Array Formula to do it all dynamically on the fly.

Vlookup multiple columns with array formula in Google Sheets

Breaking this down into steps:

3.1 Create Array of Criteria 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 and then hit Ctrl + Shift + Enter, or Cmd + Shift + Enter (Mac) to add the Array Formula designation.)

This is the result, generated by the single formula in cell B15:

Array Formula inside Vlookup Google Sheets

3.2 Add the other columns from the original table

In this step we build the new search table by adding the other columns from the original table that we want to search, using this formula:

=ArrayFormula({$A$2:$A$9&" "&$B$2:$B$9,$C$2:$D$9})

Here we’ve used the curly braces { … } to combine arrays. Using a “,” between curly brace arrays treats them as columns next to each other, which is what we want in this case, as you can see in the image:

Array Formula inside Vlookup Google Sheets

(Note 1: If we wanted arrays on top of each other, we use the “;” notation.)

(Note 2: For most European users, your syntax is a little different. Have a read of this post: Explaining syntax differences in your formulas due to your Google Sheets location.)

3.3 Perform the Vlookup

Now we have created a new table with the full name column, we simply use this as the range input in a standard vlookup, as shown in the first image of Section 3 above.

The formula to do this is:

=ArrayFormula(VLOOKUP(A15,{$A$2:$A$9&" "&$B$2:$B$9,$C$2:$D$9},3,false))

Notice that the Array Formula wrapper stays on the outside of the whole formula.

Want a copy of the example worksheet?

Yes, click here.

Related Articles

2 thoughts on “Vlookup Multiple Criteria in Google Sheets”

  1. Hi Ben, this is great but I want to do it the other way round. The range I am querying has the values in two different cells. Is that possible?

  2. Hello. Thank you for the tutorial. I used your example 3 and modified it a bit to get it to do exactly what I need.
    Now the next thing I need to do is import range, but I can’t get it to work. How would I add the import range to the example 3 below? Thanks.
    =ArrayFormula(VLOOKUP(K27&” “&L27,{$K$11:$K$18&” “&$L$11:$L$18,$M$11:$O$18},{2,3,4},false))

Leave a Reply

Your email address will not be published. Required fields are marked *