INDEX

Formulas / INDEX
To retrieve a value based on its location within a given array, vector, or reference.
=INDEX(array, row_num, [column])
  • array - a range of cells or an array constant
  • row_num - the row number in the reference or array
  • col_num - [OPTIONAL] the column number in the reference array
  • area_num - [OPTIONAL] the range to use in the reference

Examples

  • =INDEX(A1:A5,3)

    This function can be used to retrieve values from a range of cells, and will return the value in cell A3.

  • =INDEX(A1:B5,3,1)

    The INDEX function can also be used to retrieve values from a range of cells that include more than one column, and will return the value in cell A3.

  • =INDEX(A1:A5,2,2)

    This will return the value in cell B2.

  • =INDEX((A1:C5,A7:C10),1,3,2)

    This example, will return the value in A7:C10 at row 1 and column 3.


Summary

The INDEX function allows users to return a value or reference to a value in a table or range.

  • The INDEX function returns a reference not a value, and the value of a cell is typically displayed rather than the reference.
  • INDEX can retrieve individual values as well as entire rows and columns, and is frequently used with the MATCH function.
  • Using the MATCH function with INDEX gives information about a position by using MATCH to provide row and column numbers for INDEX.

Frequently Asked Questions

What is the INDEX function?
The INDEX function returns a value or the reference to a value from a table or range.
What arguments does the INDEX function take?
The INDEX function takes two required arguments:
  • The array constant argument (required in the array form of the INDEX function)
  • Either the row_num or column_num argument (required)
What does the INDEX function return?
The INDEX function returns the value in the cell from which it is called if both the row_num and column_num arguments are used. It also returns a #REF! error when the row_num or column_num do not point to a cell in the array.
Do I need to enter the INDEX function as an array formula?
No, the INDEX function does not need to be entered as an array formula in Microsoft 365.

Related Functions

Sourcetable Logo

Work smarter

Al is here to help. Leverage the latest models to
analyze spreadsheets, enrich data, and create reports.

Drop CSV