INDEX (Array Form)

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.

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.

• 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 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.
• 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. To enter an array formula, press CTRL+SHIFT+ENTER in Microsoft Excel 97 for Windows or COMMAND+ENTER in Microsoft Excel 97 for the Macintosh.

Remarks

Row_num and column_num must point to a cell within array; otherwise, INDEX returns the #REF! error value.

Examples

INDEX({1,2;3,4},2,2) equals 4

If entered as an array formula, then:

INDEX({1,2;3,4},0,2) equals {2;4}

If cells B5:B6 contain the text Apples and Bananas, and cells C5:C6 contain the text Lemons and Pears, respectively, then:

INDEX(B5:C6,2,2) equals Pears

INDEX(B5:C6,2,1) equals Bananas