VLOOKUP partial match in Excel
In Excel, use VLOOKUP with a partial match to find a string value that contains a certain criteria.
In this example, we'll find someone's email address based on the first characters of their name:
Formula in F4
=VLOOKUP(F3&"*",B4:C7,2,FALSE)
A
B
C
D
E
F
G
1
2
Column 1
Column 2
👇 Try changing this value
3
Name
Email
Lookup value
4
James
james@example.com
Result
elizabeth@example.com
5
Mary
mary@example.com
6
Elizabeth
elizabeth@example.com
7
Ashley
ashley@example.com
8
Show arrows
Separator
Procedure
- Enter =VLOOKUP in cell F4, where you want the Email address to appear.
- Enter the Lookup value F3&"*", which contains the prefix of the Name (Eli for Elizabeth) you want to look for.
The ampersand (&) combines the Lookup value with the wildcard ("*").
Placing the wildcard at the end results in a "begins with" match.
This will cause VLOOKUP to match the first entry in column B (Name) that begins with the prefix "Eli". - Enter the Search range B4:C7, the range of data containing the Name and the Email values.
- Enter Column number 2, as the Email column is the 3rd column of the Search range.
- Enter FALSE to look for an exact match.
Result
The final formula is: =VLOOKUP(F3&"*", B4:C7, 2, FALSE)
VLOOKUP returns the value in F4, which is elizabeth@example.com.