Using wildcards with VLOOKUP can be super useful for you. It can help you to look up for a value using a partial match.
Let’s say you have data like below table where you have students full name in one column and their marks in another column. Now from this data, you want to lookup for a particular student’s marks but only with the first name.
A normal VLOOKUP doesn’t allow you to lookup 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, you will learn to use wildcard characters with VLOOKUP. I have listed 5 real life examples which can help you to understand this combination.
Types of Wildcards
You have total 3 wildcard characters.
- 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 above two characters. For example, if you want to look up for a value “PD*”, instead you can use “PD~*”.
Examples: Using Wildcards With VLOOKUP
Here I have listed 5 different examples to help you understand that 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.
How Does it Work?
In 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 by using different methods, 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 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. But, unfortunately with the invoice number which you are using to look up for the amount we 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:
How Does it Work?
In 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.
Now the thing is, 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:
How Does it Work?
In above formula, these two question marks are representing the text which we have in the original product ID list.
4. 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.
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.
With wildcard characters, you can increase the power of VLOOKUP and save yourself from using complex formulas. I have explained examples all three wildcards with VLOOKUP so that you can choose them according to the situation.
I hope you found it useful.
If you ask me, an asterisk is one of most common characters which I use.
Now, it’s your turn to speak. If you have any other situation where we can use these wildcards, please share with me in the comment box.
I would love to hear from you.