VLOOKUP partial match in Google Sheets

In Google Sheets, 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
Open in Google Sheets
Show arrows
Separator

Procedure

  1. Enter =VLOOKUP in cell F4, where you want the Email address to appear.
  2. 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".
  3. Enter the Search range B4:C7, the range of data containing the Name and the Email values.
  4. Enter Column number 2, as the Email column is the 3rd column of the Search range.
  5. 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.

Show reference