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 function, you might be using COLUMN() function to extract the column index. Nevertheless, these processes are indeed very tiring, counting and counting non-stop.
If you’re not aware what I’m talking about, please refer back to the previous article that I talked about how you can retrieve information using VLOOKUP() exact match.
However, since I’d 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.
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.
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…count… But instead, this guy will get all the dirty work done.
Okay, I’m done with the talking. Now let’s see what this MATCH() has to offer.
The Syntax:
=MATCH(Lookup_Value, Array, [Match_Type])
The parameters:
LOOKUP_Value: The value that you would like the match function to use to find match from the list
ARRAY: The list where you would like the Match to run its match work
MATCH_TYPE: the type of match you would like Match to run. 0: Exact Match; -1: greater than; 1: Less than
What does it do?
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)
DEMO TIME
Example 1
So, below I showcase how you can find out the location index of the name Liza within a column.
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’re clear with the output. Enjoy…..
Example 2
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.
The answer will be returned as which column index that Liza is being placed
I hope with these 2 demo you can get the idea of what MATCH() function does. If you’re curious about how do you use the other Match_Type (-1, and 1, If you’re aware). I will talk to you about that in a bit. But now, I’ll leave the Match_Type as 0 <=zero, so it will return the result as the matched is exact match.
Integrating the MATCH() with VLOOKUP()
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.
LOOKUP for the Product Description from Candy Table
LOOKUP for the Price Per Unit from Candy Table
If you take a good look in the gif demo above, in fact, how the way the “Product Desc” and the “Price Per Unit” is almost the same, besides the Column Index Number. Simply because “Product Desc” is being located in the column Index 2 while the “Price per Unit” is located in column Index 3.
Instead of the manual way of getting the Column Index number, we use MATCH function to pass the column index to VLOOKUP().
This is how it goes.
So now you can see how awesome this integration is. I’m totally in love with this way of looking up for data. I hope you’ve enjoyed!CATEGORIESARTICLESTAGSEXCEL, VLOOKUP
