INDEX Formula in Excel


The Excel INDEX formula is designed to return a single text or numerical value from a range. INDEX is used to return the row or column or both inside of a given range. To add increased flexibility to the formula INDEX is regularly used in conjunction with the MATCH function, where the MATCH function generates the row/column position to retrieve for the INDEX formula. The two formulas are a perfect match for one another.

The INDEX formula is t he perfect location based formula.  You put a range to evaluate and tell it the Row and Column coordinates and INDEX goes to work to return the value in the specified location. 

The syntax for the INDEX formula is as follows:

INDEX(Range, Row Reference, Column Reference)

The Range is the area which contains the values you are evaluating.

The Row Reference is the Row from the top of the range, this is a number.

The Columns is the Columns from the start of the range, this is a  number.

Let’s have a look at a practical example. 

Index example

The formula to extract the value 93 in blue above is as follows:

=INDEX(B3:D8,4,3)

Where B3:D7 is the range being evaluated.  The ROW reference is 4, and the column reference is 3 as it is the third column.

It is  worth noting that the INDEX formula can be used with the row reference or the column reference or both. For example f you are sure that column 3 is the one you wish to evaluate the following can be simplified to just look for the ROW reference.  The formula becomes:


=INDEX(D3:D8,4)

Notice the range is shortened to just include Column D.  As the dataset is a single column the ROW is the only reference point required in the INDEX formula.

Index Excel Example

When D becomes the column of focus (D3:D8) then the only thing to evaluate is the row number.