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/

Objective

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:

=VLOOKUP(B7&"*",Export!B:C,2,0)

Where:

• 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!

Additional Resources

• 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.