Using Wildcard criteria as LOOKUP VALUE
Missing pieces of information of lookup value, what should you do?
What I’m bringing out next is a very common issue faced by many people and in fact, this very same issue has been brought up in the classroom many quite frequent as well. Which is, how to look up with an incomplete Lookup Value. We all know when it comes to extracting information or comparing tables using VLOOKUP can be quite fun. But, it is not going to be fun, when you constantly get #NA! as output. This happened when VLOOKUP couldn’t find a match from the table. Somehow you knew the information is there but have no idea why #NA! constantly showed up! Maybe you might have missed out the prefix or suffix character. So, if that does happened to you, how you usually solve the problem?
I have just the right solution for you. Using wildcard character to help with you search.
So, if you have no idea what wildcard is? Wildcard usually being used when you are searching and replacing content.
Wildcard has 3 different type:
|?||(Question Mark)||Any single character|
|*||(Asterisk)||From empty to literal amount of character|
|~||(Tilde)||This will help the nullify effect on wildcard character.|
Below I have a Hershay’s Product Table. Within contains all the product information about all the candy sold from a candy store.
So if I were to extract any information from this table, it’s easy. ONLY if you have the complete productid. What about I don’t quite remember with the prefix of the code. If I search using just the suffix of the Product ID, VLOOKUP will end returning #NA!
Check this out.
So, I was trying to find the information about a product with the suffix 7004. But I ended up getting #NA! instead, because I’m missing the prefix of the code.
To solve this problem, it’s easy. Wildcard is there to help. Use wildcard character while looking data using VLOOKUP. In this situation, we can use either asterisk (*) or question mark (?).
If you’re missing the prefix, the wildcard should be place in front of the lookup value while searching. You can also use this while searching for names. Example, when you’re looking for first name or last name, you can definitely use this.
Back to business, so now I will be using question mark (?) while looking up for information and this is how it goes. Instead of use 7004 as your lookup value, we search by using ?7004. The question mark is to replace the unknown prefix character. I use the question mark surround by double apostrophe (“”) and then join with the 7004 using ampersand (&). This is how it looks “?”&7004. With the question mark being placed in front of the 7004, VLOOKUP will search 7004 pretending like it is the complete version of the ProductID.
Voala! Now you can use this to search.
Now what about if the prefix sometimes comes with 1 character, sometimes 2 characters? With uncertain amount of prefix character, you should be using asterisk instead of question mark.
Which means, your Lookup Value should look like so “*”&7004.
you can also use it to find product name using (*) asterisk. Example, you can’t
remember the full name of a product, but you have partial piece of the name. In
order to retrieve the full name of a product, you can definitely use asterisk
to do the job.
Let’s assume that I only can recall a product call drops milk chocolate, but not remembering the full name of it. By using (*) asterisk, it solved my problem.
So, this is how my Lookup Value looks like. I place the asterisk in front and the back of the keywords “Drops Milk Chocolate”, the VLOOKUP will then return the full name of the product that matches with the structure of the Lookup Value -> “*”&Drops Milk Chococlate&”*”.
If you are aaware that the product name ends with “Drops Milk Chocolate” then you should just place the asterisk right in front of the keywords; or if the product name begins with “Drops Milk Chocolate” then the asterisk should go at the back.
|Begins with “Drops Milk Chocolate”||Drops Milk Chocolate&”*”|
|Ends with “Drops Milk Chocolate”||”*”&Drops Milk Chocolate|
|Contains “Drops Milk Chocolate”||“*”&Drops Milk Chocolate&”*”|