VLOOKUP with multiple values on the same row in Google Sheets
Method 1: Return the entire row
In Google Sheets, VLOOKUP cannot natively return multiple values from the same row. Use FILTER instead.
In this example, we'll find someone's name and company based on their email address:
Formula in G3
=FILTER(C$3:D$6,F3=B$3:B$6)
A
B
C
D
E
F
G
H
I
1
2
Email
Name
Company
Lookup value
Result: Name
Result: Company
3
mary@pg.com
Mary
Procter & Gamble
james@ab.com
James
Rockwell Automation
4
james@ab.com
James
Rockwell Automation
5
elizabeth@cogitize.com
Elizabeth
Cogitize
6
ashley@summables.com
Ashley
Summables
7
Show arrows
Separator
Procedure
- Enter =FILTER in cell G3, where you want the Name to appear.
- Enter the Search range C$3:D$6, which is the data range containing the Name and the Company values.You can add the dollar sign ($) to fix the rows, so if you drag down the formula, FILTER always refers to the same rows.
- Enter the Condition F3=B$3:B$6, which filters rows of the Search range where the value in column B (Email) equals the lookup value in F3.
Result
The final formula is: =FILTER(C$3:D$6, F3=B$3B$6)
FILTER returns the values in G3 and G4, which are James and Rockwell Automation.
Method 2: Only return some columns
In Google Sheets, use FILTER to look up multiple values from disjoint columns.
In this example, we'll find someone's name and company based on their email address:
Formula in C9
=FILTER({C$3:C$6,E$3:E$6},B9=B$3:B$6)
A
B
C
D
E
F
1
2
Email
First name
Last name
Company
3
mary@papersy.com
Mary
Presley
Papersy
4
james@inventive.com
James
Oakley
Inventive
5
elizabeth@cogitize.com
Elizabeth
Evangeline
Cogitize
6
ashley@summables.com
Ashley
Stark
Summables
7
8
Lookup value
Result: First name
Result: Company
9
ashley@summables.com
Ashley
Summables
10
Show arrows
Separator
Procedure
- Enter =FILTER in cell H3, where you want the First name to appear.
- Enter the Search range {C$3:C$6,E$3:E$6}, which is the data range containing the First name and the Company values.The curly braces "{}" are used to construct a custom range that can exclude some columns.
- Enter the Condition G3=B$3:B$6, which filters rows of the search range where the value in column B (Email) equals the lookup value in G3.
Result
The final formula is: =FILTER({C$3:C$6,E$3:E$6}, G3=B$3:B$6)
FILTER returns the value in H3 and I3, which are Ashley and Summables.