VLOOKUP with multiple values on the same row in Excel
Method 1: Return the entire row
In Excel, 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 Excel, use nested FILTER formulas to look up multiple values from disjoint columns.
In this example, we'll find someone's first name and company based on their email address:
Formula in C3
=FILTER(FILTER(C$3:E$6, B9=B$3:B$6),{1,0,1})
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
- Build the first FILTER formula:
- Enter =FILTER in cell C9, where you want the First name to appear.
- Enter the search range C$3:E$6, which is the data range containing the First 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 B9=B$3:B$6, which filters rows of the search range where the value in column B (Email) equals the Lookup value in B9.
- Build the second FILTER formula around the first FILTER formula:
- Enter FILTER( between the = and first FILTER formula.
The formula looks like this:=FILTER(FILTER(C$3:E$6, B9=B$3:B$6) - Enter the Condition {1,0,1}, which tells FILTER to return only the first and third column (First name and Company).1,0,1 means the 1st and the 3rd column will be returned, and the 2nd will be ignored.
- Enter FILTER( between the = and first FILTER formula.
Result
The final formula is: =FILTER(FILTER(C$3:E$6, B9=B$3:B$6), {1,0,1})
FILTER returns the values in C9 and D9, which are Ashley and Summables.