Show All

Hide All

Returns the value of an element in a table or an array (array: Used to build single formulas that produce multiple results or that operate on a group of arguments that are arranged in rows and columns. An array range shares a common formula; an array constant is a group of constants used as an argument.), 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 (array formula: A formula that performs multiple calculations on one or more sets of values, and then returns either a single result or multiple results. Array formulas are enclosed between braces { } and are entered by pressing CTRL+SHIFT+ENTER.) 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?

Example 2

The example may be easier to understand if you copy it to a blank worksheet.

How?

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.