Documentation/Calc Functions/INDEX

Function name:
INDEX

Category:
Spreadsheet

Summary:
In its simplest form, INDEX can be used to determine the value in a particular cell (specified by row and column number) within a data table. Depending on context, INDEX can also be used to determine the reference of the cell at the intersection of the specified row and column.

INDEX is a powerful function that can be utilized to look up values in multiple, non-contiguous cell ranges and can be used in an array context to return multiple values. Flexibility is increased when INDEX is used in conjunction with the MATCH function, enabling formulas to better react to changes in the input data.

Syntax:
The general syntax for the INDEX function is:
 * INDEX(Reference[; Row[; Column[; Range]]])

In the special case when the first argument has only a single column, the following simplified syntax can be used:


 * INDEX(Reference; Row)

In the special case when the first argument has only a single row, the following alternative syntax can be used:


 * INDEX(Reference; Column)

Returns:
Technically, INDEX returns a cell reference. However, depending on the formula used, the returned value may be used as a value (whose type is determined by the input data set) or a cell reference.

Arguments:
Reference is a reference list specifying the range(s) of cells occupied by the input data table(s). It takes one of the following forms:


 * A single cell range, defined by entering the cell reference for the upper left-hand cell, followed by a colon, and then the lower right-hand cell reference (for example, A1:E10).


 * Multiple cell ranges can be passed as a single argument by providing them in a separated list, surrounded by parentheses – for example, . In this case Calc will reformat your function call to use the tilde reference concatenation operator – for example  . Alternatively, you can use the tilde reference concatenation operator directly without parentheses – for example.


 * A single cell range referenced by the name of a database range (defined by selecting on the Menu bar).


 * One or more cell ranges referenced by the name of a named range. Named ranges defined using on the Menu bar can be configured to comprise multiple areas by using the tilde reference concatenation operator in the Range or formula expression field of the Define Name dialog.


 * An inline array (for example, {1, 2, 3; 4, 5, 6}).

Row is a non-negative integer value, or a reference to a cell containing such a value, that is the required row offset from the top left of the data table. In the case of an array formula, the Row argument can be entered as an inline array to retrieve multiple values. Omitting the Row argument (with two consecutive semicolons) or setting it to the special value 0 is used in the array context to indicate that values for all rows should be returned for the specified column.

Column is a non-negative integer value, or a reference to a cell containing such a value, that is the required column offset from the top left of the data table. In the case of an array formula, the Column argument can be entered as an inline array to retrieve multiple values. Omitting the Column argument (with two consecutive semicolons) or setting it to the special value 0 is used in the array context to indicate that values for all columns should be returned for the specified row.

Range is a positive integer value, or a reference to a cell containing such a value, that indicates the subrange to be used when the Reference argument comprises multiple areas. For example, if Reference is set to A1:B6~C2:D7~E3:F8, then set Range to 1 for cell range A1:B6, set Range to 2 for cell range C2:D7, and set Range to 3 for cell range E3:F8. In the case where Reference comprises multiple areas but a value for the Range argument is not specified, the default value of 1 is assumed. In the case of an array formula, the Range argument can be entered as an inline array to retrieve multiple values.

The following conditions may arise:


 * If any of Row, Column, or Range is non-numeric, then INDEX reports a #VALUE! error.


 * If any of Row, Column, or Range is a non-integer value, then INDEX truncates it to an integer.


 * If, after any truncation, either Row or Column is a negative value, then INDEX reports an invalid argument error (Err:502).


 * If, after any truncation, Row is greater than the number of rows in the cell range indexed by the Range argument, then INDEX reports an invalid argument error (Err:502).


 * If, after any truncation, Column is greater than the number of columns in the cell range indexed by the Range argument, then INDEX reports an invalid argument error (Err:502).


 * If, after any truncation, Range is less than 1 or greater than the number of cell ranges defined in Reference, then INDEX reports a #REF! error.

Additional details:

 * INDEX can be used in an array formula. For example, enter the array formula  using Ctrl+Shift+Enter rather than just Enter. Two values are returned – the value from cell B1 is displayed in the formula cell and the value from cell B3 is displayed in the cell below the formula cell.

Examples:
The first batch of examples utilize the following table of data:

The following table gives additional examples that are not related to the table of sample data above.

Related LibreOffice functions:
AREAS

CHOOSE

MATCH

ODF standard:
Section 6.14.6, part 2

Equivalent Excel functions:
INDEX