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
- If both the row_num and column_num arguments are used, INDEX returns the value in the cell at the intersection of row_num and column_num.
- If you set row_num or column_num to 0 (zero), INDEX returns the array of values for the entire column or row, respectively. To use values returned as an array, enter the INDEX function as an array formula in a horizontal range of cells for a row, and in a vertical range of cells for a column. To enter an array formula, press CTRL+SHIFT+ENTER.
-
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 worksheet.
How?- Create a blank workbook or worksheet.
- Select the example in the Help topic. Do not select the row or column headers.
Selecting an example from Help
- Press CTRL+C.
- In the worksheet, select cell A1, and press CTRL+V.
- To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.
|
|
Example 2
The example may be easier to understand if you copy it to a blank worksheet.
- Create a blank workbook or worksheet.
- Select the example in the Help topic. Do not select the row or column headers.
Selecting an example from Help
- Press CTRL+C.
- In the worksheet, select cell A1, and press CTRL+V.
- To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.
|
|
Note The formula in the example must be entered as an array formula. After copying the example to a blank worksheet, 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.