In this article I will show you the use of MATCH function with VLOOKUP function step-by-step.
But the obvious question arises "why should I use MATCH function in VLOOKUP ?"
It is because sometimes, you will face such a situation that you have to derive lots of data in specific order from some unorganised data e.g., in the below image you can easily notice that Column Names are not organised, now if we need to get data in an organised specified format, we have to write some VLOOKUP functions (almost same) row wise side-by-side, but the count of VLOOKUPs would be equal to the count of COLUMNs.
 |
| VLOOKUP with MATCH function in Excel |
 |
| Example of VLOOKUP with MATCH function in Excel |
It’s a
tedious and time taking job and moreover there might be chances of getting
wrong data if not carefully written.
But
using match function with VLOOKUP, these VLOOKUP
functions will reduced to ONE!!!
Only ONE VLOOKUP
is required if it is nested with match function.
Is it not amazing ???
So, lets
understand first what MATCH function does and then we will use it within VLOOKUP...
The syntax of match function is
MATCH( lookup_value, lookup_array,
[match_type] )
 |
| How to use MATCH function |
The output of the above formula in picture is 8., which when I counted manually is also 8.
Now,
I’ll explain one by one…
Lookup_Value :
Which value we want to look or data against which value we need (in my example it is Product 16)
Lookup_Array :
Where is this value (lookup value) exists (from more than one value) (in my example it is A2 to N2 cells)
Match_Type : (1) - Less than, (0) - Exact match & (-1) – Greater than
In excel
whenever this function runs, it’ll display a number.
We’ll
use this number in VLOOKUP’s Column
Index number. The nested formula would be like…
From this => VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
To => VLOOKUP(lookup_value, table_array, MATCH( lookup_value, lookup_array, [match_type] )
, [range_lookup])
 | |
|
Yellow highlighted part in the formula is the MATCH(), look carefully to understand the total formula. For any query please post your query in the comment box, I'll help you...
If you are still facing problem to understand what I want to convey you, please go to my YouTube video please click in the below link or you may watch it here.
YouTube VIdeo Liknk 1 YouTube VIdeo Liknk 2
So what
will happen within this nested function?
1.
VLOOKUP will search for lookup_value
using Table_array
2.
VLOOKUP will ask the column index number
to MATCH function
3.
And
match function will return the column number where the specified column name
exists.
4.
VLOOKUP will be closed using match_type.
No comments:
Post a Comment