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.

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.

**Hey listen up!** here are some of the other things about VLOOKUP, you must check.

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

## Examples: Using Wildcards With VLOOKUP

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.

### (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.

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

And now, when you trying to look up for 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 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.

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

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

#### …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.

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

To solve this problem, you need to again combine question mark wildcards with a VLOOKUP.

And the formula will be something like this:

#### …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.

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

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

Download this sample file from here to learn more.

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

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

About the Author

Puneet is using Excel since his college days. He helped thousands of people to understand the power of the spreadsheets and learn Microsoft Excel. You can ﬁnd him online, tweeting about Excel, on a running track, or sometimes hiking up a mountain.

I imported a list of countries from the internet

https://en.wikipedia.org/wiki/List_of_countries_by_GDP_(PPP)_per_capita

and there’s a space in the beginning of the name of each country and vlookup does not work. Any hint?

GREAT! Very helpful examples. I’ve never needed to use wildcards with VLOOKUP but this morning was looking for reasons why people use them.

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

You could also use Fuzzy Lookup – free from Microsoft

This is something I can use today!!! Thanks.

Thanks for the examples & techniques. One small point – your example for tilde before the * shows the formula with

“*~”

I assume this should be

“~*”

?

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

Fantastic Puneet, thanks

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

Still iam not clear with tilde,need more examples

I never seen before this type of formula super Puneet great job thanks for this stuff

Simple tips but very useful!!

I’m so glad you liked it. 🙂

Awesome use if vlookup.. 🙂

Good info….keep it up puneet!

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

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

That’s great.

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

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