Description
The INDEX function returns a value from the given row and column number. Unlike the other lookup function ( e.g. VLOOKUP and HLOOKUP) in which, required value is looked up against a given value, you have to precisely give the exact location (column & row number) of the required value to be returned.
In our example given in the image above, we already knew that our required value i.e. “89” is at the 3rd Row and 2nd column of the Table array (C6:E10).
In the large data sets and Excel models, it is not easy to accurately provide the location of every required value. Therefore both VLOOKUP and HLOOKUP provide a more easy way out for looking up values against any given value.
However, as both VLOOKUP & HLOOKUP have the limitations, that the referenced value shall be in 1st Column and Row, a nested Excel function by forming a combination of INDEX with MATCH can overcome these limitations of LOOKUP functions.
=INDEX(array,row_num,column_num)
The INDEX function’s syntax has the following arguments:
- array Required.
A range of cells, an array constant or named range.
- row_num Required.
Represent the row from array (1 as first row) in which, our desired value is present.
However, if we have only one row in array, then it automatically, will represent column numbe.
- column_num Optional
Represent the column in array (1 as first column) from which to return the required value.
NOTE:
- If the array contains only one row or column, the corresponding row_num or column_num argument is optional.
