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

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]) where array is required, If 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 proper result.

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

and the result is 67. shown in picture.

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 lookup the value, [match_type] is optional How to match, specified as -1, 0, or 1. Default is 1.


Here I have put the formula =MATCH(C4, B4:E4,0) where it will show 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 row number for the value within the array.

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 like it has shown in the picture.


Try this and this is better than any lookup function. 

 











Related video tutorials:

Leave a Reply

Your email address will not be published. Required fields are marked *

Copyright 2017-2018@Excelabcd