VLOOKUP with multiple criteria in Google Sheets

Method 1: with a helper column

In Google Sheets, VLOOKUP cannot natively search with multiple criteria. Use VLOOKUP with a helper column to join multiple criteria together.
In this example, we'll find someone's email address based on their first and last name:

Formula in H5
=VLOOKUP(H3&H4,B4:E6,4,FALSE)
A
B
C
D
E
F
G
H
I
1
2
Column 1
Column 2
Column 3
Column 4
3
Helper
First name
Last name
Email
First name
Thomas
4
AdamsPresley
Adams
Presley
adams@example.com
Last name
Oakley
5
ThomasOakley
Thomas
Oakley
thomas@example.com
Result
thomas@example.com
6
KimEvangeline
Kim
Evangeline
elizabeth@example.com
7
Open in Google Sheets
Show arrows
Separator

Procedure

  1. Create a Helper column that combines the values of the First name and the Last name columns:
    1. Enter =VLOOKUP in cell B4, which is the first cell in the column.
    2. Enter C4, the first value (Adams) you want to use as criteria.
    3. Enter & to combine two values.
    4. Enter D4, the second value (Presley) you want to use as criteria.
    5. Drag the formula down to combine the values for each row.
  2. Enter =VLOOKUP in cell H5, where you want the Email address to appear.
  3. Enter the Lookup values H3&H4, which contain the First name (Adams) and the Last name (Presley) you want to look for.
    The ampersand (&) combines the two lookup values.
  4. Enter the Search range B4:E6, which is the data range containing the Helper, the First name, the Last name and the Email values.
    The helper column must be the 1st column of the Search range.
  5. Enter Column number 4, as the Email column is the 4th column of the Search range.
  6. Enter FALSE to look for an exact match.

Result

The final formula is: =VLOOKUP(H3&H4, B4:E6, 4, FALSE)
VLOOKUP returns the value in H5, which is adams@example.com.

Show reference

Method 2: with FILTER

In Google Sheets, VLOOKUP cannot natively search with multiple criteria. Use FILTER instead.
In this example, we'll find someone's email address based on their first and last name:

Formula in G4
=FILTER(D3:D5,G2=B3:B5,G3=C3:C5)
A
B
C
D
E
F
G
H
1
2
First name
Last name
Email
First name
Thomas
3
Adams
Presley
adams@example.com
Last name
Oakley
4
Thomas
Oakley
thomas@example.com
Result
thomas@example.com
5
Kim
Evangeline
elizabeth@example.com
6
Open in Google Sheets
Show arrows
Separator

Procedure

  1. Enter =FILTER in cell G4, where you want the Email address to appear.
  2. Enter the search range D3:D5, which is the data range to filter that contains the Email values.
  3. Enter the first Condition G2=B3:B5, which filters rows of the search range where the value in column B (First name) equals the lookup value in G2.
  4. Enter the second Condition G3=C3:C5, which filters rows of the search range where the value in Column C (Last name) equals the lookup value in G3.

Result

The final formula is: =FILTER(D3:D5, G2=B3:B5, G3=C3:C5)
FILTER returns the value G4, which is thomas@example.com.

Show reference
← PreviousHow to use XLOOKUPNext →VLOOKUP with multiple values on the same row