**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