Documentation/Calc Functions/INDEX

From The Document Foundation Wiki
Jump to navigation Jump to search
Other languages:
English • ‎Nederlands



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, =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. 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 {=INDEX($A$1:$D$4; {1;3}; 2)} 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:

A B C D
1
2 4 5 6
3 red green blue
4 TRUE FALSE TRUE
Formula Description Returns
=INDEX(B2:D4; 1; 1) Here the function returns the data in cell B2, which is the number 4. Note how 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. Here the function returns the value in cell C3, which is the string "green". green
=INDEX(C3:D4; 2; 2) Here the function returns the value in cell D4, which is the Boolean value TRUE. TRUE
=INDEX(B2:B4; 3) Here the function returns the value in cell B4, which is the Boolean value TRUE. Note how this example utilizes the simplified syntax available for a single column data set. TRUE
=INDEX(B3:D3; 2) Here the function returns the value in cell C3, which is the string "green". Note how this example utilizes the simplified syntax available for a single row data set. green
{=INDEX(B2:D4; 2; 0)} where the formula is entered as an array formula, using Ctrl+Shift+Enter rather than just Enter. Here the function returns a row of three values, corresponding to the three values in the second row of the input data. The string "red" appears in the formula cell, "green" appears in the next cell to the right, with "blue" in the cell to the right of that. red, green, and blue
{=INDEX(B2:D4; 0; 2)} where the formula is entered as an array formula, using Ctrl+Shift+Enter rather than just Enter. Here the function returns a column of three values, corresponding to the three values in the second column of the input data. The number 5 appears in the formula cell, "green" appears in the cell below, with the number 0 in the cell below that (the original Boolean FALSE is converted to its numeric value in this scenario). 5, green, and 0
{=INDEX(B2:D4; {2; 3}; 3)} where the formula is entered as an array formula, using Ctrl+Shift+Enter rather than just Enter. Here the function returns a column of two values, corresponding to the values in cells D3 and D4 of the input data. The string "blue" appears in the formula cell and the value 1 appears in the cell below (the original Boolean TRUE is converted to its numeric value in this scenario). blue and 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) Note that no value is specified for the Range argument and so the default of 1 is used. Here the function returns the data in the cell that lies at the intersection of row 1 and column 1, in the first area defined in the Reference argument. The formula =INDEX(A1:C3~A5:C7~A9:C11; 1; 1; 1) gives the same result. Contents of cell A1
=INDEX(A1:C3~A5:C7~A9:C11; 1; 1; 2) Here the function returns the data in the cell that lies 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. Here the function returns the data in the cell that lies 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})} where the formula is entered as an array formula, using Ctrl+Shift+Enter rather than just Enter. ThreeAreas has been defined as a named range using the reference expression $A$1:$C$3~$A$5:$C$7~$A$9:$C$11. Here the function returns the data in the cell that lies 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 in a column. Contents of cells B2, B6, and B10
{=INDEX({1,3,5; 7,9,10}; {2;1}; 1; 1)} where the formula is entered as an array formula, using Ctrl+Shift+Enter rather than just Enter. Here the function returns the values 7 and 1, organized into two rows and one column. 7 and 1
=CELL("address"; INDEX(A2:C6; 2; 2)) Here the formula uses the reference to cell B3 returned by the INDEX function. The CELL function returns the absolute address of cell B3, that is $B$3. $B$3

Related LibreOffice functions:

AREAS

CHOOSE

MATCH

ODF standard:

Section 6.14.6, part 2

Equivalent Excel functions:

INDEX