VLOOKUP and Wildcards
In a previous post, we explored how to use the TRIM function to remove trailing spaces from lookup values used in a VLOOKUP function. In this post, we’ll tackle the reverse issue, where the values in the lookup column contain trailing spaces, by using wildcards in the VLOOKUP function.
Note: depending on your version of Excel, you may have XLOOKUP as an option … more info here: https://www.excel-university.com/xlookup/
Before we get started, let’s get a tangible example to make it easy to visualize our objective. Let’s say we exported some data from our accounting system. Here is a screenshot of the exported data:
We store the budget data in an Excel worksheet named Variance, as shown below:
Our objective is to use the VLOOKUP function to retrieve the exported actual data into column D of this worksheet.
Although this should be easy, we run into a snag. The issue lies with the fact that our accounting system report labels contain numerous trailing spaces. For example, the exported label for the first account is “40110 · Design Income ” (includes a bunch of trailing spaces). Since the lookup value doesn’t have the trailing spaces, the VLOOKUP function doesn’t make the match and returns an error. This error is demonstrated below:
This error occurs because we used exact match logic (0/FALSE for the fourth argument) and the lookup value, which is a text string that has no trailing spaces, doesn’t match any of the lookup column values, all of which contain trailing spaces.
One possible solution is to remove the trailing spaces from all lookup column values. There are numerous ways to accomplish this task, such as setting up a helper column using the TRIM function or by using FlashFill. But, these add manual steps to our process and our goal is to eliminate manual steps from recurring tasks. Thus, we prefer write a smart formula that will enable us to work with the data as it comes. This smart formula can be achieved with a simple modification to our existing VLOOKUP function. Fortunately, the VLOOKUP function supports the use of the ? and * wildcard characters.
Wildcard characters are supported in the VLOOKUP function when the lookup value is a text string and when the fourth argument indicates exact match logic (FALSE or 0). The ? wildcard will match any single character and the * character will match any number of characters. For example, the lookup value “ABC Supply*” would match “ABC Supply,” “ABC Supply Inc.,” and “ABC Supply Incorporated.” The lookup value “X100?” would match “X100A” and “X100b.” Expanding on this idea, we can see how using the * wildcard character allows the VLOOKUP function to match our account name that has no trailing spaces with lookup column values that have trailing spaces. We’ll use the concatenation operator (&) to join our lookup value with the wildcard character, as illustrated below:
- B7&”*” is the lookup value, the account in B7 and the * wildcard
- Export!B:C is the lookup range, the exported actuals
- 2 the column that has the value to return, the amount
- 0 tells the function to perform exact match
When we use this modified version of our original formula, we have success, as illustrated below:
This trailing spaces example is one application of wildcards in VLOOKUP. Hopefully, using wildcards in your VLOOKUP functions will enable you to write formulas that work with the data as it comes, eliminate manual steps, and improve productivity!
If you have any other interesting applications of wildcards and VLOOKUP, please share by posting a comment below…thanks!
- Download sample file: VLOOKUP_wildcard
- If the lookup values contain trailing spaces rather than the lookup column, try the TRIM function: TRIM post
- For more information about VLOOKUP in general, check out coupler.io’s VLOOKUP Excel Guide, which is comprehensive and very well written.
Excel is not what it used to be.
You need the Excel Proficiency Roadmap now. Includes 6 steps for a successful journey, 3 things to avoid, and weekly Excel tips.
Want to learn Excel?
Access all Undergrad and Masters lessons with a Campus Pass or CPE Pass. Includes on-demand training plus live office hours.
Is there a way to tell Excel (the VLOOKUP function) to treat two characters as interchangeable? (I want to compare two lists of words with different spelling conventions, so I want to specify that the letter may occur instead of , for example, but not just any character should count. Is that possible?
Hmmm…there is a way, but, depending on your list of words, this approach may or may not work. The basic idea is that, instead of using a wildcard character ?, which as you mention would allow ANY alternative character, you could use the SUBSTITUTE function to specify the alternate character. I’ll talk about how it works, but then list out the problems with it.
SUBSTITUTE allows you to substitute one character or text string for another. For example, instead of looking up the stored value in A1 “customize” you wanted to tell VLOOKUP to find “customise” instead. You could use SUBSTITUTE like this: VLOOKUP(SUBSTITUTE(A1,”z”,”s”)…). The thing is, that we got lucky on that one because z only appears once, exactly where we want to make the substitution. If there were two z’s, then, SUBSTITUTE would replace both and ultimately break. Another issue is that this approach won’t match the original spelling “customize” and will only find “customise.”
Other ideas to consider are using a lookup table to store and map the original and alternate spellings, or, check out Microsoft’s free fuzzy lookup Excel add-in as discussed here:
Hope these ideas help!
I find these excel tips extremely useful! The short video clips are excellent as well. thanks!
I’m trying to VLOOKUP names. However, one list sometimes have middle names in them and the other doesn’t. Is there a way to get a match?
Mr.Jeff wrote a great blog on this issue:
Let me know if that helps you:)
I’m using VLOOKUP to find matching dates(A) and Names(B) in two tables, In two books, CA, CAC. When a match is found put data from CAC, C,D,E into CA, C,D,E
Both tables are of the same length, same number of rows. CAC rows should all be used and only once.
I use this formula and although it works some data is not in the proper row, is used twice or is not used at all.
When I look at your example I wounder if it might help.
Genius stuff thanks!
My values column can be found within my array (ie, value = BMO Field, array contains “Maple Leaf Sports & Entertainment – BMO Field”). Is there a way to vlookup these values against this array? I’m trying to dedup the value field against the array. Normally my formula would be =if(vlookup(value,array,1,false)=value,”X”,””).