The INDEX function returns the value in the cell located at the intersection of the specified row and column within a collection of cells or from an array returned by an array function.
INDEX(range, row-index, column-index, area-index)
range: A collection of cells. range is either a single collection or more than one collection, each a single range of cells. If more than one collection is specified, they are separated by commas and enclosed in an additional set of parentheses. For example, ((B1:B5,C10:C12)). The cells referenced by range can contain any values.
row-index: A number value representing the row number of the value to be returned. row-index must be greater than or equal to 0 and less than or equal to the number of rows in range. If row-index is 0, INDEX returns the array of values for the entire column column-index, which must be greater than 0 and less than or equal to the number of columns in range.
column-index: An optional number value specifying the column number of the value to be returned. column-index must be greater than or equal to 0 and less than or equal to the number of columns in range. If column-index is 0, INDEX returns the array of values for the entire row row-index, which must be greater than 0 and less than or equal to the number of rows in range.
area-index: An optional number value specifying the area number of the value to be returned. area-index must be greater than or equal to 1 and less than or equal to the number of areas in range. If area-index is omitted, 1 is used.
Notes
INDEX can return the value at the specified intersection of a two-dimensional collection of values. For example, suppose that cells B1:D8 contain the values.
=INDEX(B1:D8, 2, 3) returns the value found at the intersection of the second row and third column (the value in cell D3).
More than one area can be specified by enclosing the ranges in an additional pair of parentheses. For example:
=INDEX((B2:D5, B7:D10), 2, 3, 2) returns the value at the intersection of the second row and the third column in the second area (the value in cell D8).
INDEX can return a one-row or one-column array for another function. In this form, either row-index or column-index is required, but the other argument can be omitted. For example:
=SUM(INDEX(B2:D5, , 3)) returns the sum of the values in the third column (cells D2 through D5).
=AVERAGE(INDEX(B2:D5, 2)) returns the average of the values in the second row (cells B3 through D3).
INDEX can return (or “read”) the value from an array returned by an array function. The FREQUENCY function returns an array of values, based on specified intervals.
=INDEX(FREQUENCY($A$1:$F$5, $B$8:$E$8), 1) would return the first value in the array returned by the given FREQUENCY function.
Similarly =INDEX(FREQUENCY($A$1:$F$5, $B$8:$E$8), 5) would return the fifth value in the array.
The location in the array is specified by indicating the number of rows down and the number of columns to the right in relation to the cell in the upper-left corner of the array.
Except when INDEX is specified as shown in the third case above, row-index can’t be omitted, and if column-index is omitted, it is assumed to be 1.
Examples |
|---|
Given the following table: |
A | B | C | D | |
|---|---|---|---|---|
1 | 1 | 11 | 21 | |
2 | 2 | 12 | 22 | |
3 | 3 | 13 | 23 | |
4 | 4 | 14 | 24 | |
5 | a | b | c | |
6 | d | e | f | |
7 | g | h | i | |
8 | j | k | l |
=INDEX(B1:D4, 2, 3) returns 22, the value in the second row (row-index is 2) and third column (column-index is 3) of the specified collection (range is B1:D4 so the content of cell D2 is the result). =INDEX((B1:D4, B5:D8), 2, 3, 2) returns “f”, the value in the second row (row-index is 2) and third column (column-index is 3) of the second area (area-index is 2), which is cell D6. =SUM(INDEX(B1:D4, , 3)) returns 90, the sum of the values in the third column (cells D1 through D4). =AVERAGE(INDEX(B1:D4, 2)) returns 12, the average of the values in the second row (cells B2 through D2). |