How to use VLOOKUP for wildcard matches

Abstract

In this video we’ll look at how to use theVLOOKUP functionwith wildcards.

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 at previously.

This time, however, notice that the ID column has been moved into the data.

We’ll cover that in a separate video.

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

If I pop in in “Irons” I get the ID for Julie Irons.

But notice that I need to pop in in the full last name.

How can we adjust theVLOOKUPformula to allow a partial match?

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 that the asterisk is in the formula, there’s no need to enter it manually.

Notice that this is not a perfect solution.

As always, in the case of duplicates, VLOOKUP will return the first match.