{"id":814,"date":"2023-06-19T14:52:41","date_gmt":"2023-06-19T06:52:41","guid":{"rendered":"https:\/\/www.info-trek.com\/blog\/?p=814"},"modified":"2024-05-02T12:10:17","modified_gmt":"2024-05-02T04:10:17","slug":"vlookup-101-part-4","status":"publish","type":"post","link":"https:\/\/www.info-trek.com\/blog\/vlookup-101-part-4\/","title":{"rendered":"VLOOKUP 101 Part 4"},"content":{"rendered":"\n

Using Wildcard criteria as LOOKUP VALUE<\/h3>\n\n\n\n

Missing pieces of information of lookup value, what should you do?<\/em><\/p>\n\n\n\n

What I\u2019m 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\u2019t 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?<\/p>\n\n\n\n

I have just the right solution for you. Using wildcard character to help with you search.<\/p>\n\n\n\n

So, if you have no idea what wildcard is? Wildcard usually being used when you are searching and replacing content.<\/p>\n\n\n\n

Wildcard has 3 different type:<\/p>\n\n\n\n

?<\/td>(Question Mark)<\/td>Any single character<\/td><\/tr>
*<\/td>(Asterisk)<\/td>From empty to literal amount of character<\/td><\/tr>
~<\/td>(Tilde)<\/td>This will help the nullify effect on wildcard character.<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

Below I have a Hershay\u2019s Product Table. Within contains all the product information about all the candy sold from a candy store.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

So if I were to extract any information from this table, it\u2019s easy. ONLY if you have the complete productid. What about I don\u2019t quite remember with the prefix of the code. If I search using just the suffix of the Product ID, VLOOKUP will end returning #NA!<\/p>\n\n\n\n

Check this out.<\/p>\n\n\n\n

So, I was trying to find the information about a product with the suffix 7004. But I ended up getting #NA! instead, because I\u2019m missing the prefix of the code.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

To solve this problem, it\u2019s 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 (?).<\/p>\n\n\n\n

If you\u2019re 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\u2019re looking for first name or last name, you can definitely use this.<\/p>\n\n\n\n

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 (\u201c\u201d) and then join with the 7004 using ampersand (&). This is how it looks \u201c?\u201d&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.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Voala! Now you can use this to search.<\/p>\n\n\n\n

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.<\/p>\n\n\n\n

Which means, your Lookup Value should look like so \u201c*\u201d&7004.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Besides, you can also use it to find product name using (*) asterisk. Example, you can\u2019t 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.<\/p>\n\n\n\n

Let\u2019s 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.<\/p>\n\n\n\n

So, this is how my Lookup Value looks like. I place the asterisk in front and the back of the keywords \u201cDrops Milk Chocolate\u201d, the VLOOKUP will then return the full name of the product that matches with the structure of the Lookup Value -> \u201c*\u201d&Drops Milk Chococlate&\u201d*\u201d.<\/p>\n\n\n\n

If you are aaware that the product name ends with \u201cDrops Milk Chocolate\u201d then you should just place the asterisk right in front of the keywords; or if the product name begins with \u201cDrops Milk Chocolate\u201d then the asterisk should go at the back.<\/p>\n\n\n\n

Begins with \u201cDrops Milk Chocolate\u201d<\/td>Drops Milk Chocolate&\u201d*\u201d<\/td><\/tr>
Ends with \u201cDrops Milk Chocolate\u201d<\/td>\u201d*\u201d&Drops Milk Chocolate<\/td><\/tr>
Contains \u201cDrops Milk Chocolate\u201d<\/td>\u201c*\u201d&Drops Milk Chocolate&\u201d*\u201d<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
\"\"<\/figure>\n\n\n\n
\"\"<\/a><\/figure>\n\n\n\n

CATEGORIESGENERAL<\/a>TAGSEXCEL<\/a>, VLOOKUP<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"

Using Wildcard criteria as LOOKUP VALUE Missing pieces of information of lookup value, what should you do? What I\u2019m 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 […]<\/p>\n","protected":false},"author":4,"featured_media":605,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-814","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-uncategorized"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.info-trek.com\/blog\/wp-json\/wp\/v2\/posts\/814","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.info-trek.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.info-trek.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.info-trek.com\/blog\/wp-json\/wp\/v2\/users\/4"}],"replies":[{"embeddable":true,"href":"https:\/\/www.info-trek.com\/blog\/wp-json\/wp\/v2\/comments?post=814"}],"version-history":[{"count":1,"href":"https:\/\/www.info-trek.com\/blog\/wp-json\/wp\/v2\/posts\/814\/revisions"}],"predecessor-version":[{"id":815,"href":"https:\/\/www.info-trek.com\/blog\/wp-json\/wp\/v2\/posts\/814\/revisions\/815"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.info-trek.com\/blog\/wp-json\/wp\/v2\/media\/605"}],"wp:attachment":[{"href":"https:\/\/www.info-trek.com\/blog\/wp-json\/wp\/v2\/media?parent=814"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.info-trek.com\/blog\/wp-json\/wp\/v2\/categories?post=814"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.info-trek.com\/blog\/wp-json\/wp\/v2\/tags?post=814"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}