How to use the VLOOKUP Function with Match Function- Advanced Part 4 & 5

In this article I will show you the use of MATCH function with VLOOKUP function step-by-step.

Click here to Download the working File

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])

How to use VLOOKUP with MATCH function

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.