The field she needed to search contained a variable number of characters as a prefix, followed by the check number, then ending with even more characters. Most people don’t realize that VLOOKUP will support wildcards. In fact, the Excel team at Microsoft thinks that wildcards more frequently end up in VLOOKUPs by accident than they do by people using them intentionally. Since most people don’t know that VLOOKUP will support wildcards, it seems more likely that people will perform a VLOOKUP where their lookup value happens to contain a character that wasn’t intended to represent a wildcard.
USING WILDCARDS
The standard wildcards are an asterisk (*) to mean any number of characters, and a question mark (?) to mean any one character. What if you actually have to look for an asterisk or a question mark? Use ~* to search for an asterisk or ~? to search for a question mark. Use ~~ to search for a tilde.
Using these three characters are particularly difficult if you have hundreds of VLOOKUPs and the values already contain wildcard characters. Trying to convert the values on the fly using nested SUBSTITUTE functions becomes unwieldy. Imagine =VLOOKUP(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, “~”,“~~”),“*”,“~*”),“?”,“~?”). It would actually be better if Microsoft offered an option that turned off the wildcard behavior for a particular worksheet or workbook.
Back to the original problem. You would have to search for *apple* to find the word “apple” inside of any text in the first column of the lookup table. But since cell A2 of Figure 1 already contains “apple,” the formula starts with =VLOOKUP(“*”&A2&“*”. In this case, you’re using the concatenation character (&) to wrap the value in A2 with an asterisk on each side. Alternatively, you could use =VLOOKUP(CONCATENATE( “*”,A2,“*”) to wrap the word “apple” in asterisks.
The formula =VLOOKUP(“*”&A2&“*”,$D$2:$E$6,2,FALSE) correctly finds the $956 from cell E5.
Use extra care in reviewing the results from the wildcard lookup. What if you were searching for both “apple” and “crabapple”? The search for crabapple would be correct, but since VLOOKUP returns the first match in the lookup table, the VLOOKUP for *apple* would return the value for crabapple if it’s found before apple in the lookup table.
FINDING AND SUMMING ALL MATCHES
As I worked through the example at the Excel seminar, someone else asked about the situation where there might be multiple matches and you’d like to find and sum all of the matching results.
Since the *apple* wildcard worked so well with VLOOKUP, I created a data set where we could test to see if SUMIFS would allow wildcards as well. In the SUMIFS function shown below, the formula says to add up the values in column E if the corresponding value in column D contains *Apple*. This formula works as well.
IMPROVING WILDCARD HANDLING
Are you surprised to learn that VLOOKUP can handle wildcards? Have you ever done a VLOOKUP where the lookup value might contain an asterisk or question mark as part of normal text and not intended to be used as a wildcard?
If the answer to either of those questions is yes, then it probably would make sense to you for Microsoft to offer a new, improved version of VLOOKUP that ignores wildcards by default. There could be an extra optional argument at the end of this new function that would allow you to tell Excel to use wildcards. If you left that argument off, then Excel wouldn’t use any wildcards. You could easily look for “142*154” or “Why?” or “e*Trade” without having to worry about a value like “Electrical Trade” showing up as a false match.
FASTER VLOOKUPS ARE COMING
Microsoft has been testing a new, speedier version of VLOOKUP in the Insider Monthly channel of Office 365 builds for the last several months. The function is often 10 times as fast as the old VLOOKUP, particularly when there are thousands of lookups against the same table.
The new logic should be rolling out to all Office 365 subscribers by the end of 2019. If your VLOOKUPs are currently taking less than a second, you may not notice the change. But if you have a massive worksheet that takes 40 seconds to calculate, expect to see that recalculation time drop to about four seconds or less.
Download this month’s file: 1905Excel_Wildcards.
SF SAYS
Wildcard handling in VLOOKUP is a little-known feature of Excel.
May 2019
