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

VLOOKUP<\/h3>\n\n\n\n

I would say, VLOOKUP function is one of the infamous function in Excel. I would say, almost 90% of Excel Power user, which is YOU will never feel complete not involving VLOOKUP in their job routine. Some people might say they use VLOOKUP to compare the older list with the newer list; Some might say they use VLOOKUP to extract information; Some may say they use VLOOKUP to search information; Some might say they use VLOOKUP to join or merge table etc.<\/p>\n\n\n\n

Whichever way you use VLOOKUP, I will cover most of the ways of above mentioned. But first, you need to understand what VLOOKUP is.<\/p>\n\n\n\n

VLOOKUP\u2019s lil brother<\/h3>\n\n\n\n

Just in case you\u2019re not aware, VLOOKUP has a twin brother called HLOOKUP. So, in what situation you will be needing that lil brother? That will depend on the Table that you\u2019re looking up (or comparing) from.<\/p>\n\n\n\n

If your Table is aligned vertically which is how typically a list would look like, you will be needing VLOOKUP<\/strong>, since the V<\/strong> in VLOOKUP<\/strong> stands for Vertical.<\/p>\n\n\n\n

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

If your Table is aligned horizontally, you will be needing HLOOKUP<\/strong>, since the H<\/strong> in HLOOKUP<\/strong> stands for Horizontal.<\/p>\n\n\n\n

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

Lookup mode<\/h3>\n\n\n\n

VLOOKUP and HLOOKUP each has 2 different mode while looking up for the data. First will be exact match mode while another one will be approximately match mode.<\/p>\n\n\n\n

Exact Match<\/strong> mode usually used to lookup for identifier or ID, simply because identifier is usually unique. So, whenever the VLOOKUP found its one and only match from the lookup table, it will return results from the table. Some of you might curious about redundant ID. Sorry, VLOOKUP will only return result based of the first matched ID. But of course, if you wonder is there any solution to that. The answer is YES (I will cover that in a bit, not in this series).<\/p>\n\n\n\n

Approximately Match<\/strong> mode is often used to lookup for number range. When this mode is turned ON and VLOOKUP is on its mission, it will look for the closest match number within the number list. In fact, it tries to lookup for the biggest number that is less than itself. In another form of explanation is, it uses \u201c>=\u201d comparison mode while searching for the closest matched number from the list. This will be covered in detail later on.<\/p>\n\n\n\n

VLOOKUP is always RIGHT<\/strong><\/h3>\n\n\n\n

How ? How come VLOOKUP is always right? This sis because VLOOKUP only look towards right. As mentioned, VLOOKUP is being used to extract information. But how does VLOOKUP know what to extract? You will have to tell VLOOKUP what to extract and based on what LookupValue. Once it has found its matched, it will begin to travel to right to extract whatever information that is located at the right side.<\/p>\n\n\n\n

Which also means, the LookupValue must be able to be found in the first column of the table, after that it will goes right.<\/p>\n\n\n\n

This is how I suggest your table should look like.<\/p>\n\n\n\n

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

Some might tell me, \u201cIsn\u2019t as long the information that I would like to extract is located at the right side of the LookupValue will do?\u201d Urm, I personally wouldn\u2019t recommend that is because \u201cI don\u2019t wanna to miss a thing<\/a>\u201d. That song literally hit me the moment I wrote that.<\/p>\n\n\n\n

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

My best suggestion is, if you do realize that the Lookup column is not being located in the first column, you should do everything you could to make that happen.<\/p>\n\n\n\n

Start Counting 1..2..3..<\/h3>\n\n\n\n

When the \u201clooking right\u201d situation is going on, VLOOKUP is busy looking for which column should it be extracting as the output. This is also depending on what you would like to extract. If you are extracting the Product based on the Sales_ID, the count of the column index number will be 5; If you are extracting the Profit based on the Sales_ID, then the count will be 8 then.<\/p>\n\n\n\n

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

HLOOKUP is always DOWN<\/strong><\/h3>\n\n\n\n

Since the HLOOKUP will take the whole lookup experience in the horizontal way, so once it has found its matched, it will begin to look down for the extraction. Once it begins to look down, it will start counting the row index since it is going down.<\/p>\n\n\n\n

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

HLOOKUP and VLOOKUP they are basically the same<\/h3>\n\n\n\n

So after a long winded explanation, can you find the similarity between these 2 functions? They are basically the same, but when to use who is simply based on how your table is being align and that\u2019s it.<\/p>\n\n\n\n

In the next article I\u2019m covering how VLOOK can be used to solve some of the problem face.<\/p>\n","protected":false},"excerpt":{"rendered":"

VLOOKUP I would say, VLOOKUP function is one of the infamous function in Excel. I would say, almost 90% of Excel Power user, which is YOU will never feel complete not involving VLOOKUP in their job routine. Some people might say they use VLOOKUP to compare the older list with the newer list; Some might […]<\/p>\n","protected":false},"author":4,"featured_media":201,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-820","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\/820","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=820"}],"version-history":[{"count":1,"href":"https:\/\/www.info-trek.com\/blog\/wp-json\/wp\/v2\/posts\/820\/revisions"}],"predecessor-version":[{"id":821,"href":"https:\/\/www.info-trek.com\/blog\/wp-json\/wp\/v2\/posts\/820\/revisions\/821"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.info-trek.com\/blog\/wp-json\/wp\/v2\/media\/201"}],"wp:attachment":[{"href":"https:\/\/www.info-trek.com\/blog\/wp-json\/wp\/v2\/media?parent=820"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.info-trek.com\/blog\/wp-json\/wp\/v2\/categories?post=820"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.info-trek.com\/blog\/wp-json\/wp\/v2\/tags?post=820"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}