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:
- 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.
The final formula is:
=VLOOKUP(F3&"*", B4:C7, 2, FALSE)
VLOOKUP returns the value in F4, which is email@example.com.