{"id":816,"date":"2023-06-19T14:53:41","date_gmt":"2023-06-19T06:53:41","guid":{"rendered":"https:\/\/www.info-trek.com\/blog\/?p=816"},"modified":"2024-07-12T14:33:00","modified_gmt":"2024-07-12T06:33:00","slug":"vlookup-101-prt-3","status":"publish","type":"post","link":"https:\/\/www.info-trek.com\/blog\/vlookup-101-prt-3\/","title":{"rendered":"VLOOKUP 101 PRT.3"},"content":{"rendered":"\n

Extracting information using VLOOKUP()-approximately Match mode<\/h4>\n\n\n\n

I know I know, some of you must have been complaining the pain in keep having to count the column index number for VLOOKUP, so that VLOOKUP will be able to retrieve the information according to the column number given. And some people might be slightly knowledgeable with excel function, you might be using COLUMN() function to extract the column index. Nevertheless, these processes are indeed very tiring, counting and counting non-stop.<\/p>\n\n\n\n

If you\u2019re not aware what I\u2019m talking about, please refer back to the previous article that I talked about how you can retrieve information using VLOOKUP() exact match.<\/p>\n\n\n\n

However, since I\u2019d mention the issue face by majority of you EXCEL PRO USER out there. I am definitely have a solution for you, which is get some help from our buddy MATCH() function. In some cases I would say MATCH() function itself standalone is not quite a useful function. But it does provide awesome info to the other function, like a helper function. With the output returned by this MATCH() function, it definitely helps A LOT with the complaint made by all of you.<\/p>\n\n\n\n

So who is this helper guy, MATCH()? Well, MATCH() is a function where it will return the location index of your lookupValue. In away, it tells you which row or column that it is located using index starting from 1. You might be curious, why do I have to specifically say from 1? If you have a lil knowledge about programming, majority of the programming language starts their index from ZERO.<\/p>\n\n\n\n

Anyways, back to our topic. So yea, this brother tells you where your lookupValue are using Index number. Which means, with this function, you will no longer needing to count..count\u2026count\u2026 But instead, this guy will get all the dirty work done.<\/p>\n\n\n\n

Okay, I\u2019m done with the talking. Now let\u2019s see what this MATCH() has to offer.<\/p>\n\n\n\n

The Syntax:<\/strong><\/p>\n\n\n\n

=MATCH(Lookup_Value, Array, [Match_Type])<\/p>\n\n\n\n

The parameters:<\/strong><\/p>\n\n\n\n

LOOKUP_Value: The value that you would like the match function to use to find match from the list<\/p>\n\n\n\n

ARRAY: The list where you would like the Match to run its match work<\/p>\n\n\n\n

MATCH_TYPE: the type of match you would like Match to run. 0: Exact Match; -1: greater than; 1: Less than<\/p>\n\n\n\n

What does it do?<\/strong><\/p>\n\n\n\n

Returns the relative position of an item in an array that matched a specific value in a specific order. It simply means it tells you where it is being located using Index\/ number within a range (it can be a row or column form)<\/p>\n\n\n\n

DEMO TIME<\/h3>\n\n\n\n

Example 1<\/strong><\/p>\n\n\n\n

So, below I showcase how you can find out the location index of the name Liza within a column.<\/p>\n\n\n\n

The answer will be returned as which row index that Liza is being placed. I also demo the other name, not just Liza. Just to make sure you\u2019re clear with the output. Enjoy\u2026..<\/p>\n\n\n\n

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

Example 2<\/strong><\/p>\n\n\n\n

So, below I showcase how you can find out the location index of the name Liza within a row. Which is the opposite of how I list the names.<\/p>\n\n\n\n

The answer will be returned as which column index that Liza is being placed<\/p>\n\n\n\n

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

I hope with these 2 demo you can get the idea of what MATCH() function does. If you\u2019re curious about how do you use the other Match_Type (-1, and 1, If you\u2019re aware). I will talk to you about that in a bit. But now, I\u2019ll leave the Match_Type as 0 <=zero, so it will return the result as the matched is exact match.<\/p>\n\n\n\n

Integrating the MATCH() with VLOOKUP()<\/h3>\n\n\n\n

I will use back the example that I used in the previous article. A customer was requesting for the quotation for the item code. And one of our procurement admin uses the VLOOKUP to extract the product description and price into the table so finally she can summarize the Total Payable amount before sending it back to the customer.<\/p>\n\n\n\n

LOOKUP for the Product Description from Candy Table<\/strong><\/p>\n\n\n\n

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

LOOKUP for the Price Per Unit from Candy Table<\/strong><\/p>\n\n\n\n

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

If you take a good look in the gif demo above, in fact, how the way the \u201cProduct Desc\u201d and the \u201cPrice Per Unit\u201d is almost the same, besides the Column Index Number. Simply because \u201cProduct Desc\u201d is being located in the column Index 2 while the \u201cPrice per Unit\u201d is located in column Index 3.<\/p>\n\n\n\n

Instead of the manual way of getting the Column Index number, we use MATCH function to pass the column index to VLOOKUP().<\/p>\n\n\n\n

This is how it goes.<\/p>\n\n\n\n

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

So now you can see how awesome this integration is. I\u2019m totally in love with this way of looking up for data. I hope you\u2019ve enjoyed!CATEGORIESARTICLESTAGSEXCEL,\u00a0VLOOKUP<\/p>\n","protected":false},"excerpt":{"rendered":"

Extracting information using VLOOKUP()-approximately Match mode I know I know, some of you must have been complaining the pain in keep having to count the column index number for VLOOKUP, so that VLOOKUP will be able to retrieve the information according to the column number given. And some people might be slightly knowledgeable with excel […]<\/p>\n","protected":false},"author":4,"featured_media":251,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-816","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\/816","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=816"}],"version-history":[{"count":2,"href":"https:\/\/www.info-trek.com\/blog\/wp-json\/wp\/v2\/posts\/816\/revisions"}],"predecessor-version":[{"id":2408,"href":"https:\/\/www.info-trek.com\/blog\/wp-json\/wp\/v2\/posts\/816\/revisions\/2408"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.info-trek.com\/blog\/wp-json\/wp\/v2\/media\/251"}],"wp:attachment":[{"href":"https:\/\/www.info-trek.com\/blog\/wp-json\/wp\/v2\/media?parent=816"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.info-trek.com\/blog\/wp-json\/wp\/v2\/categories?post=816"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.info-trek.com\/blog\/wp-json\/wp\/v2\/tags?post=816"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}