Excel - Microsoft Excel Index Function

Description

The Excel Index function returns a reference to a cell that lies in a specified row and column of a user-supplied range of cells. 

There are two formats of the function, which are the Array Format (which is the most basic format), and the Range Format of the function. 

The Array Format of the Excel INDEX Function

The Array format of the Index function is the most basic form of the function, and is used when you want to look up a reference to a cell within a single range. 

The syntax of the function is : 

INDEX( array, row_num, col_num ) 

Where the arguments are as follows : 

Excel Index Function (Array Format) Examples

Example 1

In the following example, the Index function returns a reference to row 5 of the supplied range, which is cell C5. This evaluates to the value 8

Example 2

In the following example, the Index function returns a reference to row 5 and column 2 of the supplied range, which is cell D5. This evaluates to the value 3

Example 3

In the following example, as the supplied col_num is set to 0, the Index function returns a reference to all of row 5 of the supplied range, which is the range C5:D5. 

The sum of this range evaluates to the value 11

Array Formulas: 

To input an array formula, you need to first highlight the range of cells for the function result. Type your function into the first cell of the range, and press CTRL-SHIFT-Enter. 

Example 4

In the following example, the supplied row_num is set to 0, and so the Index function returns a reference to all of column 2 of the supplied range. I.e. the function returns a reference to the range D1:D5. 

In this case, the function is input into cells A1:A5, as an Array Formula. Therefore, the values of the five cells returned by the Index function are displayed in cells A1:A5. 

The Range Format of the Excel INDEX Function

The Range format of the Index function can be used to extract references from ranges that are made up of more than one area. 

The format of the function is : 

INDEX( range, row_num, col_num, area_num ) 

Where the arguments are as follows : 

Excel Index Function (Range Format) Examples

Example 1

In the following example, the Index function returns a reference to row 4 and column 2 of the 1st area (ie. C1:D5) in the supplied range. This is cell D4. This evaluates to the value 5

Example 2

In the following example, the Index function returns a reference to row 3 and column 1 of the 3rd area (ie. B10:D12) in the supplied range. This is cell B12, which evaluates to the value 7

Example 3

In the following example, as the supplied col_num is blank, the Index function returns a reference to all of row 3 of the 3rd area (ie. B10:D12) of the supplied range. This is the range B12. 

The sum of this range evaluates to the value 10. 

Index Function Errors

If you get an error from the Excel Index function this is likely to be one of the following: 

Common Errors