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: