Documentation/Calc Functions/INDEX

TDF LibreOffice Document Liberation Project Community Blogs Weblate Nextcloud Redmine Ask LibreOffice Donate

Other languages:

Calc Functions Home | List of functions | Abbreviations

Function name:

INDEX

Category:

Spreadsheet

Summary:

In its simplest form, INDEX can be used to retrieve the value in a particular cell, specified by relative row and column numbers 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 data value (whose type is determined by the input data set) or as 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 (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, =INDEX((A1:B6; C2:D7; E3:F8); …). In this case Calc will reformat your function call to use the tilde reference concatenation operator – for example =INDEX((A1:B6~C2:D7~E3:F8); …). Alternatively, you can use the tilde reference concatenation operator directly without parentheses – for example =INDEX(A1:B6~C2:D7~E3:F8; …).
A single cell range referenced by the name of a database range (defined by selecting Data ▸ Define Range on the Menu bar).
One or more cell ranges referenced by the name of a named range. Named ranges defined using Sheet ▸ Named Ranges and Expressions ▸ Define 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. In the array context, omitting the Row argument (with two consecutive semicolons) or setting it to the special value 0 (zero) indicates 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. In the array context, omitting the Column argument (with two consecutive semicolons) or setting it to the special value 0 (zero) indicates that values for all columns should be returned for the specified row.

Range is a positive integer value (defaults to 1), 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 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 { =INDEX($A$1:$D$4; {1;3}; 2) } using Ctrl+Shift+Enter ("CSE") 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.
For inline array values, the respective separators to be used in formulas can be configured in Tools ▸ Options ▸ LibreOffice Calc ▸ Formula, in the Separators area.

Examples:

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

	B	C	D
1
2	4	5	6
3	red	green	blue
4	TRUE	FALSE	TRUE

Formula Description Returns

=INDEX(B2:D4; 1; 1) Returns the data in cell B2. The Row and Column arguments are offsets relative to the supplied cell range, not relative to the spreadsheet. 4

=INDEX(DataTable; G1; G2)

where DataTable is the name of a database range encompassing the cell range B2:D4 and where cells G1 and G2 both contain the number 2.

Returns the value in cell C3, which is the intersection of the second row with the second column within DataTable.

green

=INDEX(C3:D4; 2; 2) Returns the value in cell D4. TRUE

=INDEX(B2:B4; 3) Returns the value in the third cell, B4. Utilizes the simplified syntax available for a single-column data set. TRUE

=INDEX(B3:D3; 2) Returns the value in the second cell, C3. Utilizes the simplified syntax available for a single-row data set. green

{ =INDEX(B2:D4; 2; 0) }

using CSE.

Returns a row of the three values from the second row of the input data.

The string "red" appears in the formula's cell; "green" appears in the next cell to the right, with "blue" in the cell to the right of that.

red	green	blue

{ =INDEX(B2:D4; 0; 2) }

using CSE.

Returns a column of the three values from the second column of the input data.

The number 5 appears in the formula's cell; "green" appears in the cell below, with the number 0 (zero) in the cell below that. The original Boolean FALSE is converted to its numeric value in this scenario.

5
green
0

{ =INDEX(B2:D4; {2; 3}; 3) }

using CSE.

Returns a column of the second and third values from the third column of the reference range; cells D3 and D4 of the input data.

The string "blue" appears in the formula's cell and the value 1 appears in the cell below. The original Boolean TRUE is converted to its numeric value in this scenario.

blue
1

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

Formula Description Returns

=INDEX(A1:C3~A5:C7~A9:C11; 1; 1)

=INDEX(A1:C3~A5:C7~A9:C11; 1; 1; 1)

When no value is specified for the Range argument, the default of 1 is used.

Returns the data in the cell that resides at the intersection of row 1 and column 1, in the first area defined in the Reference argument.

Contents of cell A1

=INDEX(A1:C3~A5:C7~A9:C11; 1; 1; 2) Returns the data in the cell that resides at the intersection of row 1 and column 1, in the second area defined in the Reference argument. Contents of cell A5

=INDEX(ThreeAreas; 3; 3; 3)

where ThreeAreas has been defined as a named range using the reference expression $A$1:$C$3~$A$5:$C$7~$A$9:$C$11.

Returns the data in the cell that resides at the intersection of row 3 and column 3, in the third area defined in the Reference argument.

Contents of cell C11

{ =INDEX(ThreeAreas; 2; 2; {1;2;3}) }

using CSE. ThreeAreas has been defined as a named range using the reference expression $A$1:$C$3~$A$5:$C$7~$A$9:$C$11. {1;2;3} is a one-column inline array of 3 rows for the Range argument, using semicolon as row-separator.

Returns the data in the cell that resides at the intersection of row 2 and column 2, in each of the three areas defined in the Reference argument. The three values are returned, arranged in a column.

Contents of cells B2, B6, and B10

{ =INDEX({1,3,5; 7,9,10}; {2;1}; 1; 1) }

using CSE. {1,3,5; 7,9,10} is a 3-columns by 2-rows inline array, using semicolon as row-separator and comma as column-separator.

Organized in one column of two rows: from the second line, return the first value; from the first line, return the first value.