Returns a value or the reference to a value from within a table or range. There are two forms of the INDEX() function: array and reference. The array form always returns a value or an array of values; the reference form always returns a reference.
INDEX(array,row_num,column_num) returns the value of a specified cell or array of cells within array.
INDEX(reference,row_num,column_num,area_num) returns a reference to specified cells within reference.
Returns the value of an element in a table or an array, selected by the row and column number indexes.
The INDEX function has two syntax forms: array and reference. The array form always returns a value or array of values; the reference form always returns a reference. Use the array form if the first argument to INDEX is an array constant.
Syntax 1
Array form
INDEX(array,row_num,column_num)
Array is a range of cells or an array constant.
If array contains only one row or column, the corresponding row_num or column_num argument is optional.
If array has more than one row and more than one column, and only row_num or column_num is used, INDEX returns an array of the entire row or column in array.
Row_num selects the row in array from which to return a value. If row_num is omitted, column_num is required.
Column_num selects the column in array from which to return a value. If column_num is omitted, row_num is required.
Remarks
Row_num and column_num must point to a cell within array; otherwise, INDEX returns the #REF! error value.
Example 1
The example may be easier to understand if you copy it to a blank spreadsheet.
Selecting an example from Help
Data | Data |
---|---|
Apples | Lemons |
Bananas | Pears |
Formula | Description (Result) |
=INDEX(A2:B3,2,2) | Value at the intersection of the second row and second column in the range (Pears) |
=INDEX(A2:B3,2,1) | Value at the intersection of the second row and first column in the range (Bananas) |
Example 2
The example may be easier to understand if you copy it to a blank spreadsheet.
Selecting an example from Help
Formula | Description (Result) |
---|---|
=INDEX({1,2;3,4},0,2) | Value in the first row, second column in the array constant (2) |
Value in the second row, second column in the array constant (4) |
Note The formula in the example must be entered as an array formula. After copying the example to a blank spreadsheet, select the range A2:A3, starting with the formula cell. Press F2, and then press CTRL+SHIFT+ENTER. If the formula is not entered as an array formula, the single result is 2.
Returns the reference of the cell at the intersection of a particular row and column. If the reference is made up of nonadjacent selections, you can pick the selection to look in.
The INDEX function has two syntax forms: array and reference. The array form always returns a value or an array of values; the reference form always returns a reference.
Syntax 2
Reference form
INDEX(reference,row_num,column_num,area_num)
Reference is a reference to one or more cell ranges.
If you are entering a nonadjacent range for the reference, enclose reference in parentheses.
If each area in reference contains only one row or column, the row_num or column_num argument, respectively, is optional. For example, for a single row reference, use INDEX(reference,,column_num).
Row_num is the number of the row in reference from which to return a reference.
Column_num is the number of the column in reference from which to return a reference.
Area_num selects a range in reference from which to return the intersection of row_num and column_num. The first area selected or entered is numbered 1, the second is 2, and so on. If area_num is omitted, INDEX uses area 1.
For example, if reference describes the cells (A1:B4,D1:E4,G1:H4), then area_num 1 is the range A1:B4, area_num 2 is the range D1:E4, and area_num 3 is the range G1:H4.
Remarks
If you set row_num or column_num to 0 (zero), INDEX returns the reference for the entire column or row, respectively.
Example
The example may be easier to understand if you copy it to a blank spreadsheet.
Selecting an example from Help
Fruit | Price | Count |
---|---|---|
Apples | 0.69 | 40 |
Bananas | 0.34 | 38 |
Lemons | 0.55 | 15 |
Oranges | 0.25 | 25 |
Pears | 0.59 | 40 |
Almonds | 2.80 | 10 |
Cashews | 3.55 | 16 |
Peanuts | 1.25 | 20 |
Walnuts | 1.75 | 12 |
Formula | Description (Result) | |
=INDEX(A2:C6,2,3) | The intersection of the second row and third column in the range A2:C6, which is the content of cell C3. (38) | |
=INDEX((A1:C6,A8:C11),2,2,2) | The intersection of the second row and second column in the second area of A8:C11, which is the content of cell B9. (3.55) | |
=SUM(INDEX(A1:C11,0,3,1)) | The sum of the third column in the first area of the range A1:C11, which is the sum of C1:C6. (216) |