INDEX function

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.

Description

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 Then see
Return the value of a specified cell or array of cells Array form
Return a reference to specified cells Reference form

Array form

Description

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.

Syntax

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.

Remark

 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.

Examples

Example 1

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.

Example 2

This example uses INDEX in an array formula.

Example 3

Top of Page Top of Page

 
 
Applies to:
Excel 2010, Excel Web App, SharePoint Online for enterprises, SharePoint Online for professionals and small businesses , Excel 2007, Excel 2003