This article describes the formula syntax and usage of the INDEX function (function: A prewritten formula that takes a value or values, performs an operation, and returns a value or values. Use functions to simplify and shorten formulas on a worksheet, especially those that perform lengthy or complex calculations.) in Microsoft Excel.
Returns a value or the reference to a value from within a table or range. There are two forms of the INDEX function: the 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.) form and the reference form.
|If you want to
|Return the value of a specified cell or array of cells
|Return a reference to specified cells
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.
Use the array form if the first argument to INDEX is an array constant.
INDEX(array, row_num, [column_num])
The INDEX function syntax has the following arguments (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.):
- 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.
Note In Excel Web App you cannot create array formulas.
- Row_num and column_num must point to a cell within array; otherwise, INDEX returns the #REF! error value.
Use the embedded workbook shown here to work with examples of this function. You can inspect and change existing formulas, enter your own formulas, and read further information about how the function works.
This example uses INDEX to return the value of the cell at the intersection of the rows and columns specified in the formulas.
To work in-depth with this workbook, you can download it to your computer and open it in Excel. For more information, see the article Download an embedded workbook from OneDrive and open it on your computer.
This example uses INDEX in an array formula.
Top of Page