Exceljet

Quick, clean, and to the point

How to use VLOOKUP for wildcard matches

In this video, we'll look at how to use VLOOKUP with wild cards. This is useful when you want to allow a lookup based on a partial match.

Let's take a look.

Here we have the employee list we've looked previously. This time, however, notice that the ID column has been moved into the data. It's no longer the first column in the data, so we can't use it to find values with VLOOKUP.

This is actually a good example of a situation where you may need to INDEX and MATCH instead of VLOOKUP if you need to match on the ID. We'll cover that in a separate video.

However, we can still use VLOOKUP in this case to match on the last name.

First, I'll name the data in the table "data", and the name the first cell in the search form, "last".

No I can enter the first VLOOKUP formula, using last for lookup value, data for the table, 3 for column and 0 for exact match.

This works fine. If I type in "Irons" I get the ID for Julie Irons.

But notice I need to type in the full last name. If I type in Iron, I get NA, even though that's a close match.

How can we adjust the VLOOKUP formula to allow a partial match?

You might be temped to change the last argument from 0 to one, to allow "approximate" matching, but this won't work.

Instead, we want to keep zero as the last argument (to force an exact match), and adjust the lookup value in the function to include an asterisk as a wildcard. I can already do this manually. If I enter "iron*", VLOOKUP finds the right ID. Because the asterisk matches one or more characters, "Iro*" also works. So does "Ir*".

If I want, I can also adjust the formula to automatically do a wildcard match. To do this, I just need to concatenate the asterisk to the lookup value inside the function. As always literal text needs to be in double quotes.

Now I can copy the formula down, and adjust the VLOOKUP functions to get the right values.

Notice that this is not a perfect system. If I enter "ba*" we get the information for Steven Batista, even though there are other last names that begin with "ba". As always, in the case of duplicates, VLOOKUP will return the first match.

However, it's easy to adjust the lookup value to be more specific, so it's still a handy tool when you want to do partial matches.

Course 

Related shortcuts

Author 
Dave Bruns