INDEX(array, row_num, [column_num])

The INDEX function syntax has the following arguments.

  • Array    Required. 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    Required. Selects the row in array from which to return a value. If Row_num is omitted, Column_num is required.

  • Column_num    Optional. 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.

Use MATCH instead of one of the LOOKUP functions when you need the position of an item in a range instead of the item itself. For example, you might use the MATCH function to provide a value for the row_num argument of the INDEX function.