INDEX
Formulas / INDEXTo 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.
