How to use INDEX function with MATCH function, replace VLOOKUP, speed up processing time

22 June 2017

How to use INDEX function with MATCH function, replace VLOOKUP, speed up processing time

Hi Friends,

Today I will show you how to use INDEX function with MATCH function instead of using VLOOKUP step-by-step.

Free Download INDEX() MATCH() MATCH() Microsoft Excel File

Now the important question that comes first in our mind, "Why should I not use VLOOKUP ?"

Obviously VLOOKUP is the most useful and easy function to use. If VLOOKUP is used with other functions i.e., nested with other function its data extracting power increases very high and to some extent we can make it dynamic (using MATCH in place of col_index_num which I've explained and shown in my Youtube video, please refer to VLOOKUP with MATCH function).

Although VLOOKUP has great advantages and uses in Microsoft Excel based data but in some situations there is requirement of some tweak in VLOOKUP or the VLOOKUP function is not sufficient.

Why INDEX MATCH function combined more beneficial than VLOOKUP is a different question. You can easily found numerous sites explaining this in www.google.com, therefore, I'll not discuss it here.

The only thing is the processing time, the processing time in VLOOKUP is very high as compared to INDEX & MATCH function as because VLOOKUP searches the LOOKUP_VALUE in the whole range provided but when INDEX() MATCH() MATCH() function is used it only searches through the specified ROWs and COLUMNs, and therefore, the processing time is relatively less.

In practical situation you can feel this while dealing with large vloumns of data in Microsoft Excel. You'll not feel this difference while using it in small data.

Now let's explain how to use INDEX(), MATCH(), MATCH() in Microsoft Excel.

Before going to the nested formula lets analyze INDEX() function first.

The Syntax of INDEX() is :

INDEX(array, row_num, [column_num])

As the parameters are now common to us, we'll move further.

Array is the Table, actually array is "a set of organized data", it might be a row or a column or both. Arraysmay of multi-dimentional (We'll not discuss here).

Now as the syntax of INDEX() shows, we can supply the array and can provide the row number and column number and it'll display the data in the position provided by (row, column), as the below picture shows.

How to use INDEX function

Now if we look at the Syntax of MATCH() function, it looks like....

MATCH(Lookup_value, lookup_array, [match_type])

Here, in this MATCH() function we provide the lookup value, lookup value an the match type (which is usually '0' for Exact match) then, it'll show a number. Now, we'll use it in INDEX() function's second and third parameter as shown in the below picture. And it's done.

How to use INDEX function

Now if we look at the final nested function's Syntax it becomes ...

INDEX(array, MATCH(Lookup_value, lookup_array, [match_type])

, MATCH(Lookup_value, lookup_array, [match_type]))

The first MATCH() function in red color is for ROW number calculations and the second MATCH() in brown color is for COLUMN number calculations.

Now in example file it looks like

=INDEX(Sheet1!$A:$T,MATCH(Sheet2!$B8,Sheet1!$A:$A,0),MATCH(Sheet2!$C$7,Sheet1!$A$1:$Y$1,0))

and the picture is ...