How to use Wildcards with VLOOKUP in Excel (Formula)

I’m sure you want to make VLOOKUP more powerful. Well, using wildcards with VLOOKUP can do this for you. It can help you to look up a value using a partial match.

Now:

Let’s say you have data like the below table where you have students’ full names in one column and their marks in another column.

And from this data, you want to look up a particular student’s marks but only with the first name. A normal VLOOKUP doesn’t allow you to look up a value like this.

But, when you combine an asterisk which is a wildcard character, you can get the marks of a student by just using a partial match. So today in this post, I’d like to share with you how to use wildcard characters with VLOOKUP.

And for this, I have listed 5 real-life examples which can help you to understand this combination.

Types of Wildcards

Now the thing is: You have a total of  3 wildcard characters which you can use in Excel.

• Asterisk (*): Find any number of characters after a text. For example, you can use “Ex*” to match the text “Excel” from a list.
• Question Mark (?): Use a question mark to replace with a character. For example, you can use P?inter to lookup for the text “Painter” or “Printer”.
• Tilde (~): It can nullify the impact of the above two characters. For example, if you want to look up a value “PD*”, instead you can use “PD~*”.

Learn more about wildcard characters from this ultimate guide.

Here I have listed 5 different examples to help you understand how this combination of a wildcard and VLOOKUP works. You can download this sample file from here to follow along.

VLOOKUP with a First Name and an Asterisk

Let’s continue with the example I have shown you above. Here you have a list of names (First + Last) and you have to look up the marks for the students just using the first name.

When you use a normal VLOOKUP then it will return an error #N/A which means the value is not there in the list which is absolutely right.

But, when you combine the lookup value with an asterisk, you get the students’ marks without any error.

This is how this formula works

In the above formula, you have used an asterisk after the first name which helps VLOOKUP to lookup up a value that starts with the first name you have mentioned and the rest of the value can be anything.

Combine VLOOKUP and an Asterisk to avoid Trailing Spaces

Now in this data, you just have first names but again you are not able to get the marks. And the reason is, that after removing the last names the in-between space is left there.

And now, when you try to look up the marks with the first name, you are again getting an error #N/A.

But yes, you can remove all these unwanted spaces, but here our motive is to use wildcard characters with VLOOKUP to solve this problem.

Here also you have to use the same formula which you have used above. It will ignore spaces while matching a value and return the marks with the student’s first name.

Partial Invoice Look Up

In the above two examples, you have used an asterisk to perform a partial match for VLOOKUP. But here in this example, we have invoice data with the sales amount.

Here is a twist:

Every invoice number has a prefix text “INV” at the start. Unfortunately with the invoice number which you are using to look up the amount, you don’t have that text.

And with normal VLOOKUP it’s not possible to get the sales amount.

And by using a wildcard the formula will look something like this:

How this formula works

In the above formula, you have combined three question marks with the invoice number as a prefix. As I mentioned above, a question mark will represent characters.

Here these 3 question marks are representing those 3 characters that you have at the start of the invoice numbers.

And it makes it happen to lookup up the sales amount for the use with a partial match.

Use a Question Mark with VLOOKUP to match a PRODUCT ID

Let’s do it again, in this example where you have product IDs that are a combination of 4 numbers, 3 texts, and 3 numbers. Now, you need to lookup for the amount of a particular product.

This is crazy:

In this data, you only know the starting number of the product ID and the last number is the same in all the IDs. But the problem is the middle part text which you don’t have.

To solve this problem, you need to again combine question mark wildcards with a VLOOKUP. And the formula will be something like this:

How this formula works

In the above formula, these two question marks represent the text we have in the original product ID list.

Nullifying the effect of Wildcard Characters in VLOOKUP

There will be a situation where you need to remove the effect of wildcards. Let’s have look at the below data where we have values in the column with an asterisk. And here these asterisks are not used as a wildcard character but they are part of the actual values.

You might be wondering:

But when you try to look up a value that includes an asterisk, Excel will it treat as a wildcard character instead of a normal value.

So here you need to nullify the effect of that wildcard character. For this, you have to use a tilde. And you need to use a formula like this:

When you use a tilde before an asterisk and question mark it will nullify its effect of it and Excel will treat it as a normal text character.