Using wildcards with Excel formulas
Till now, we have learned the general use of Excel wildcard characters.
But that’s not it. Wildcard characters are the most useful when used in pair with other functions of Excel.
Most importantly, the VLOOKUP, and COUNTIF functions. Time we see how to do so? Let’s dive into it.
How to use wildcards with VLOOKUP
Using the VLOOKUP function, you can search for a value from any dataset in Excel.
That’s true – but how can you use the VLOOKUP function when you are unsure about the value to be looked up?
Using the wildcard characters 🎯
The data in the image below has a list of locations – and for each of them, we want to find the number of umbrellas sold.
The number of umbrellas sold sits in another dataset (the lookup table) as shown below.
We want to match our entries in the ‘Location’ column with the ‘Location TXT’ column in the lookup table.
But here’s a problem – the lookup table has certain codes at the end of each location’s name.
Applying the VLOOKUP function using the names of locations only won’t yield us a thing. See for yourself.
We have set up the VLOOKUP formula that tells Excel to return the number of umbrellas sold in NewYork City from the lookup table.
= VLOOKUP (A2, A13:B18, 2, 0)
And guess what? Excel only returns the #N/A error. This is because the lookup table doesn’t contain simple locations but locations with codes.
To help this situation out, we need to perform a partial match. So, rewrite the VLOOKUP function as follows.
=VLOOKUP(A2&”*”, A13:B18, 2, 0)
Did you notice something different about the above function? The lookup value.
We added an ampersand (the concatenate function) next to our lookup value, followed by an asterisk.
So technically, we changed the lookup value from simple A2 (location name) to A2&* (location name and any number of characters after it).
Excel now looks for all values that have NewYork City, New York to them. Irrespective of what comes after this text string.
And Ta Da! Excel finds the number of umbrellas sold for NewYork City from the lookup table.
Turn the lookup table reference into an absolute reference. And drag and drop the results to all the locations.
Excel finds and returns the number of umbrellas sold for each location.
Even though we didn’t know the exact codes for each location, the asterisk replaced it and helped us extract the results based on a partial match. ✌