In this post, you’ll learn how to use the VLOOKUP With Wildcard in Google Sheets.
VLOOKUP With Wildcard
Imagine we have this table of employee information and we’ve been given a partial name (in this example: “Mye“) to find and retrieve information about.
To do this we use a standard VLOOKUP with wildcard asterisk character: *
The idea here is that we’ll search through the Last Name column to find the one starting with “Mye”, even though we don’t know the full surname.
We use this formula to achieve that, e.g. to look up first name:
G$2&"*" searches for the string “Mye*” where the * is known as a wildcard and represents a string of anything, or nothing, that could follow on after “Mye”. In other words, it would match “Mye”, “Myers”, “Mye123”, “MyeABC123!@#”,…etc.
The rest of the formula is just a regular
See the workbook here and feel free to make your own copy (File > Make a copy…).
If you can’t access the template, it might be because of your organization’s Google Workspace settings. If you right-click the link and open it in an Incognito window you’ll be able to see it.
VLOOKUP with two wildcards
What if our partial match potentially has missing characters before as well as after it?
No problem! We can simply add a second wildcard in front of the partial match and proceed as before. The formula for first name becomes:
Since the wildcard characters can also represent nothing (i.e. no characters), then it doesn’t matter if there turns out to be nothing in front of (or after) our partial string.
- It’s necessary to use FALSE as the last argument of your VLOOKUP in order for the wildcard matching to work.
- If there are multiple matches on the wildcard string in your data table, then the first one is returned, same as the usual application of VLOOKUP.
The workbook with single and double wildcard lookups is available here.