# 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

- 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 (array formula: A formula that performs multiple calculations on one or more sets of values, and then returns either a single result or multiple results. Array formulas are enclosed between braces { } and are entered by pressing CTRL+SHIFT+ENTER.) 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.

** 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.