The Excel INDEX Function
Function Description
The Excel Index function returns a reference to a cell that lies in a specified row and column of a 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. These are described separately below.
The Array Format of the Excel INDEX Function
The Array format of the Index function 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:
| array | - | The specified array or range of cells. |
| row_num | - |
Denotes the row number of the specified array. If set to zero or blank, this defaults to all rows in the supplied array. |
| [col_num] | - |
Denotes the column number of the specified array. If set to zero or blank, this defaults to all columns in the supplied array. |
Note that the row_num and the [col_num] arguments cannot both be zero or blank.
Excel Index Function (Array Format) Examples
Example 1
In the following example, the Index function returns a reference to row 5 of the range C1:C5, which is cell C5. This has the value 8.
|
Formula
|
Result
|
Example 2
In the following example, the Index function returns a reference to row 5 and column 2 of the range C1:D5, which is cell D5. This has the value 3.
|
Formula
|
Result
|
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.
In the example, the result of the Index function is then passed into the Sum function and so the formula returns the sum of the range C5:D5, which is 11.
|
Formula
|
Result
|
In example 3 above, the Index function returned the range C5:D5. If we had wanted to enter the values of each of the cells, C5 and D5 into our spreadsheet, the Index function would need to be entered as an array formula. This is illustrated in the example below.
Example 4
Array Formulas:
To input an array formula, you need to first highlight the range of cells that will contain the function result. Type your function into the first cell of the range, and press Ctrl + Shift + Enter.
Go to the Excel Array Formulas page for more details.In the following example, the supplied row_num is 0, and so the Index function returns a reference to all of column 2 of the supplied range, C1:D5. 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. This can be seen by the curly brackets surrounding the function in the formula bar of the Result spreadsheet.
|
Formula
|
Result
|
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 syntax of the function is:
INDEX( range, row_num,
[col_num], [area_num] )
Where the arguments are as follows:
| range | - |
The specified array or range of cells. Note: If multiple areas are input directly into the function, the individual areas should be separated by commas and surrounded by brackets - ie. ( A1:B2, C3:D4, etc). |
| row_num | - |
Denotes the row number of the specified area. If set to zero or blank, this defaults to all rows of the specified area within the supplied range. |
| [col_num] | - |
Denotes the column number of the specified area. If set to zero or blank, this defaults to all columns of the specified area within the supplied range. |
| [area_num] | - |
If the initial specified range is made up of more than one area, the [area_num] argument specifies the number of the area to be used. (Note that the areas are numbered by the order they are specified). If the [area_num] argument is omitted, it defaults to the value 1 (i.e. the reference is taken from the first area in the supplied range. |
Note that either (but not both) of the row_num or the [col_num] arguments may be zero or blank. If they are both zero or blank, the Index function will return an error.
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 in the supplied range. This is cell D4, which evaluates to the value 5.
|
Formula
|
Result
|
Example 2
In the following example, the Index function returns a reference to row 3 and column 1 of the 3rd area in the supplied range. This is cell B12, which evaluates to the value 7.
|
Formula
|
Result
|
Example 3
zero 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 of the supplied range. This is the range B12:D12.
The result of the Index function is then passed into the SUM function, which returns the value 10.
|
Formula
|
Result
|
For further details and examples of the Excel Index function, see the Microsoft Office website.
Index Function Errors
If you get an error from the Excel Index function this is likely to be one of the following:
Common Errors
| #REF! | - |
Occurs if either:
|
| #VALUE! | - | Occurs if any of the supplied row_num, [col_num] or [area_num] arguments are non-numeric. |