Lesson#41: Combination of INDEX and MATCH function is better than any other lookup function

The INDEX function returns a value or reference of the cell at the intersection of a particular row and column, in a given range.

Syntax of this function is INDEX(array, row_num, [column_num])

The array is required, If the array contains only one row or one column then put the value only for row_num or column_num. If the array has both more than one row and column then it is necessary to put both the values of row_num or column_num to get the proper result.

Here I have shown an example where formula =INDEX(B1:E8, 2,2) is put to find out the value at the intersection of row no 2 and column no 2 of the B1:E8 array.

and the result is 67. shown in the picture.

The MATCH function returns the position of an item in an array that matches a specified value in a specified order.

Syntax of this function is =MATCH (lookup_value, lookup_array, [match_type]) where lookup_value is The value to match in lookup_array, lookup_array is the array from where you have to look up the value, [match_type] is optional How to match, specified as -1, 0, or 1. The default is 1.


Here I have put the formula =MATCH(C4, B4:E4,0) where it will show the column number for the value within the array.

Another example of finding out the row number where I have put the formula =MATCH(C4, C1:C8,0) where it will show the row number for the value within the array.

A combination of these two functions can easily replace any lookup function.
I have put the formula =INDEX(B1:E8,MATCH(B10,B1:B8,0),MATCH(B11,B1:E1,0)) 

The MATCH formula within the INDEX will search the row_num or column_num and INDEX will find out the value as it has shown in the picture.


Try this and this is better than any lookup function. 

Related video tutorials: