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.

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?

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

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?

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

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:

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

(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.

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?

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

so what if Bob Davis would have 2 payments and you would only want to see the newest payment?

Hi Ben, thank you for the tutorial.

I’d learn like to learn how the methods using vlookup and arrayformula would differ in effect. I’ve tried both and it seems to yield a similar effect. Thanks!

Thanks for sharing…. This seems really cumbersome. I really want to switch out to SQL database would make it so much easier. But I need to share with colleagues so using sheets is the best bet.

Is there an easier way? Should I use GAS?

Part 2 of my comment….

I just found the Filter function… it seems useful for having multiple criteria.