There are two wildcard characters in Excel:
- an asterisk (*) which matches one or more characters, and
- a question mark (?) matches a single character.
To use the asterisk character, we’ll use student names and numbers as a worked example.
In the first case (Cell B4 below), we’ll show that a standard VLOOKUP can be used and the asterisk (*) can be used in the SearchFor cell B4
In the second case, we’ll show you how to build the wildcard characters into your formula so you only need to type in the part of the string to search for in column B (see cell B5)
So the formula in Scenario A (cell C4) is our standard:
And the formula in Scenario B (cell C5) includes concatenation, using the & to join the asterisks to the SearchFor term to the wildcard asterisks
=VLOOKUP(“*” & B5 & “*”,E:F,2,FALSE)
A couple of things to note:
- If there are multiple matches, VLOOKUP will return only the first one
- You can do the same as the above for the question mark (?) wildcard character but you need to enter one question mark for each missing character in the search term so it’s more precise but also more fiddly to get right
vlookup-partial-match-example can be downloaded here
Want to get good at using the VLOOKUP function?
Download our practical VLOOKUP tutorial workbook with step-by-step instructions and examples to help you become a vlookup pro.