Documentation/Calc Functions/INDEX

From The Document Foundation Wiki
Jump to navigation Jump to search


Function name:

INDEX

Category:

Spreadsheet

Summary:

INDEX returns a subrange, specified by row and column number, or an optional range index. Depending on context, INDEX returns a reference or content.

Syntax:

INDEX(Reference[; Row[; Column[; Range]]])

Returns:

Returns a value using a row and column index value and optionally an area index.

Arguments:

Reference is a reference, entered either directly or by specifying a range name. If the reference consists of multiple ranges, you must enclose the reference or range name in parentheses.

If Reference is a one-dimensional column vector, Column is optional or can be omitted as an empty parameter (two consecutive ;; semicolons). If Reference is a one-dimensional row vector, Row is optional, which effectively makes Row act as the column offset into the vector, or can be omitted as an empty parameter (two consecutive ;; semicolons).

Row is an integer from range 1 to 1,048,576 or a reference to a cell containing that number which represents the row index of the reference range, for which to return a value. In case of zero (no specific row) all referenced rows are returned.

Column is an integer from range 1 to 1024 or a reference to a cell containing that number which represents the column index of the reference range, for which to return a value. In case of zero (no specific column) all referenced columns are returned.

Range is a positive whole number or a reference to a cell containing that number which represents the index of the subrange if referring to a multiple range.

  • If Row or Column has a value greater than the dimension of the corresponding given area Range, an error is returned.

Additional details:

None

Examples:

Formula Description
=INDEX(Prices;4;1) The function returns the value from row 4 and column 1 of the database range defined in Data - Define as Prices.
=INDEX(SumX;4;1) The function returns the value from the range SumX in row 4 and column 1 as defined in Sheet - Named Ranges and Expressions - Define.
=INDEX(A1:B6;1) The function returns a reference to the first row of A1:B6.
=INDEX(A1:B6;0;1) The function returns a reference to the first column of A1:B6.
=INDEX((multi);4;1) The function indicates the value contained in row 4 and column 1 of the (multiple) range, which you named under Sheet - Named Ranges and Expressions - Define as multi. The multiple range may consist of several rectangular ranges, each with a row 4 and column 1. If you now want to call the second block of this multiple range enter the number 2 as the range parameter.
=INDEX(A1:B6;1;1) The function indicates the value in the upper-left of the A1:B6 range.
=INDEX((multi);0;0;2) The function returns a reference to the second range of the multiple range.

Related LibreOffice functions:

AREAS

CHOOSE

ODF standard:

Section 6.14.6, part 2

Equivalent Excel functions:

INDEX