The Excel INDEX Function
Home »
Excel-Built-In-Functions »
Excel-Index-Function
|
Search this site:
The Excel INDEX function takes a reference to one (or more) ranges of cells and returns a reference
to the cell (or range of cells) that is specified by a row and/or column number.
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 covered separately below.
|
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 format of the function is :
INDEX( Array, Row_Num, Col_Num )
Where the arguments are as follows :
| Array |
The initial array or range of cells |
| Row_Num |
The row number from the initial supplied Array
If set to zero or blank, this denotes all rows in the supplied Array |
| Col_Num |
The column number from the initial supplied Array
If set to zero or blank, this denotes all columns in the supplied Array |
The function is best explained through the examples below.
|
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 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 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 11.
|
|
|
|
Example 4
In the following example, as the supplied Row_Num is set to 0, the Index function returns a reference
to all of column 2 of the supplied range, which is the range D1:D5. The sum of this range evaluates to 26.
|
|
|
Note that, in the above examples, when the INDEX function finds just one cell, it will evaluate the cell
before showing this in the cell. However, do be aware that the function is actually returning a reference
to the cell or range of cells that it finds.
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 :
| Range |
The initial 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 |
The row number from the initial supplied Range
If set to zero or blank, this denotes all rows in the supplied Range |
| Col_Num |
The column number from the initial supplied Range
If set to zero or blank, this denotes all columns in the supplied Range |
| Area_Num |
If the initial supplied Range is made up of more than one area, this argument specifies
the number of the area to be used.
(Note that the areas are numbered by the order they are specified) |
Again, the function is best explained via examples.
