How 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 for a value using a partial match.

Now:

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

data table use wildcards with vlookup

And from this data, you want to look up for a particular student’s marks but only with the first name.

A normal VLOOKUP doesn’t allow you to look up for 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

Examples: Using Wildcards With VLOOKUP

(1) 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 for 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 for a value which starts with the first name you have mentioned and rest of the value can be anything.

(2) 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, 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 trying to look up for the marks with the first name, you are again getting an error #N/A.

use wildcards with vlookup to lookup first name with space
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 student’s first name.

(3) 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 an 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 for 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

…this is 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 which you have in the starting of the invoice numbers.

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

(4) 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 number.

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 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

…this is 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.

(5) 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 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 for a value which 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 the effect of it and excel will treat it as a normal text character.

Sample File

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 most common characters which I use.

I hope you found this tip it 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.

You must Read these Next

  1. Wildcards with SUMIF: If we use wildcard characters with SUMIF, we can sum values using partial criteria. The good news is...
  2. Excel 3D Reference: 3D reference is a range of cells in which you can refer to the same cells from multiple worksheets...
  3. INDEX and MATCH: The combination of these two functions can match the gap of all other functions which we use for lookup...
  4. VLOOKUP with Power Query: You can use POWER QUERY to match two column and get values (By using Merge Option)...

Content Protection by DMCA.com
2018-11-16T06:13:20+00:00

9 Comments

  1. janu 8 Dec, 18 at 1:18 am - Reply

    Still iam not clear with tilde,need more examples

  2. Robert 11 Aug, 18 at 5:28 am - Reply

    Simple tips but very useful!!

    • Puneet 7 Sep, 18 at 3:55 am - Reply

      I’m so glad you liked it. 🙂

  3. Ahmed Sheikh 4 Aug, 18 at 6:23 pm - Reply

    Good info….keep it up puneet!

    • Puneet 7 Sep, 18 at 3:55 am - Reply

      Stay tuned, you’ll get some awesome stuff in coming days. 🙂

  4. yogirajoo 9 Oct, 17 at 6:44 am - Reply

    Learnt new things about VLOOKUP ! Many thanks for sharing !!

    • Puneet 7 Sep, 18 at 3:55 am - Reply

      That’s great.

  5. Krish 3 Oct, 17 at 1:37 pm - Reply

    Need more example on (~) concept in vlookup?? Plz….

    • Puneet Gogia 4 Oct, 17 at 8:03 am - Reply

      Thanks for the input. I’ll do it soon.

Leave A Comment