How to use Wildcards with VLOOKUP in Excel

HomeExcel FormulasHow to use Wildcards with VLOOKUP in Excel

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.

data table use wildcards with vlookup

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 for 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 a list of names (First + Last) and you have to look up the marks for the students just using the first name.

data table use wildcards with vlookup

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.

use wildcards with vlookup to lookup first name

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

use wildcards with vlookup to lookup first name with asterisk

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.

first name data to use with wildcard characters with vlookup

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

use wildcards with vlookup to lookup first name with space

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

use wildcards with vlookup to lookup first name with space using asterisk min

Here also you have to use the same formula which you have used above. It will ignore spaces while matching a value and will 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” in the starting. Unfortunately with the invoice number which you are using to look up the amount you don’t have that text.

invoice data to use wildcards with vlookup

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

a normal invoice lookup partial match min

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

partial invoice lookup with wildcards min

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 starting 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 which is a combination of 4 numbers, 3 texts, and 3 numbers. Now, you need to lookup for the amount of a particular product.

product id data to use wildcards with vlookup

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.

simple product id lookup with wildcards min

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

product id lookup with question mark wildcard min

How this formula works

In the above formula, these two question marks are representing the text which 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 the part of the actual values.

wrong lookup an asterisk min

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:

nullfying the effect of wildcard min

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.

sample-file.xlsx

Conclusion

With wildcard characters, you can increase the power of VLOOKUP and save yourself from using complex formulas.

I have explained examples of all three wildcards with VLOOKUP so that you can choose them according to the situation.

If you ask me, an asterisk is one of the most common characters which I use. I hope you found this tip useful, but now, it’s your turn to speak.

What are some other ways where we can combine VLOOKUP with Wildcards?

Please share your views with me in the comment section. I’d love to hear from you, and please, don’t forget to share this post with your friends, I am sure they will appreciate it.

🔙 Excel Formulas

21 thoughts on “How to use Wildcards with VLOOKUP in Excel”

  1. Hi,
    Puneet ji,

    How can I match between two invoice numbers using vlookup formula ?
    i.e T06/21/141018 & TO6/21/141018 ?
    T06/19/141018 & T06/21/141018 ?
    T06/21/141018 & TO6/19/141018 ?

    Reply
    • I used to work on data with phone numbers and at that time I realized that the use of wildcard characters is REAL. 🙂

      Reply
  2. Thanks for the examples & techniques. One small point – your example for tilde before the * shows the formula with
    “*~”
    I assume this should be
    “~*”
    ?

    Reply
  3. how to lookup value when ~ is there in lookup value and data both.
    like
    12~34 Rick
    12~35 Jack

    how to lookup name against
    12~34

    Reply
  4. Can you limit this only to look for a numerical value or a character in the question mark or asterisk?

    Such as finding this “CL123..” but not this “CLAMP …”

    Reply

Leave a Comment