# Difference between revisions of "Documentation/How Tos/Calc: INDEX function"

Javier Lopez (Talk | contribs) |
Jim DeLaHunt (Talk | contribs) (→INDEX function in array formula context: Add new section. Refer to Issue 125743) |
||

Line 70: | Line 70: | ||

<tt>'''INDEX(B3:C3; 2)'''</tt> | <tt>'''INDEX(B3:C3; 2)'''</tt> | ||

: returns <tt>'''blue'''</tt>. | : returns <tt>'''blue'''</tt>. | ||

+ | |||

+ | === INDEX function in array formula context === | ||

+ | |||

+ | Like other functions, INDEX may be used in an [[Documentation/How Tos/Using Arrays|array formula context]]. When entered as an array formula, the ''row'', ''column'', and ''areanumber'' parameters — which are expected to be scalars — can be supplied as arrays instead. | ||

+ | |||

+ | This example shows a ''row'' parameter supplied as a two-row vector, and the ''column'' and ''areanumber'' parameters as non-zero scalars. These parameters ask INDEX to return a reference to a single cell. The array context expands this to multiple rows with a single column. | ||

+ | |||

+ | <tt>'''=INDEX({1;3;5|7;9;10};{2|1};1;1)'''</tt>, entered as an array formula using '''Command-Shift-Enter''' | ||

+ | : returns array <tt>'''{7|1}'''</tt>, with two rows and one column. | ||

+ | |||

+ | Here instead, the ''row'' and ''areanumber'' parameters are non-zero scalars, and the ''column'' parameter is zero. These parameters ask INDEX to return a reference to all columns of one row in the datatable. The array context expands this to a result to enough cells to hold all columns of the row. | ||

+ | |||

+ | <tt>'''=INDEX({1;3;5|7;9;10};2;0;1)'''</tt>, entered as an array formula using '''Command-Shift-Enter''' | ||

+ | : returns array <tt>'''{7; 9; 10}'''</tt>, with one row and three columns. | ||

+ | |||

+ | '''N.B.''' If you both use an array parameter and ask for a complete row to be returned, then all but the first cells of the row are returned as ''#VALUE!'' errors instead of as the values in the datatable. This behaviour is seen in OpenOffice.org Calc version 4.0.1, and is the subject of [https://issues.apache.org/ooo/show_bug.cgi?id=125743 issue 125743]. It might be correct, and might be an error. | ||

+ | |||

+ | <tt>'''=INDEX({1;3;5|7;9;10};{2|1};0;1)'''</tt>, entered as an array formula using '''Command-Shift-Enter''' | ||

+ | : returns array <tt>'''{7; #VALUE!; #VALUE! | 1; #VALUE!; #VALUE!}'''</tt>, with two row and three columns, but errors in place of expected values. | ||

+ | |||

{{Documentation/SeeAlso| | {{Documentation/SeeAlso| |

## Revision as of 02:32, 12 October 2014

## INDEX

Returns a value from a table, given a row and column number.

### Syntax:

**INDEX(datatable; row; column; areanumber)**

- returns the value at position
,**row**in the range or array**column**.**datatable**

- If
has multiple ranges then**datatable**specifies which is to be used.**areanumber**is optional and defaults to**areanumber**.**1**

- If
has a single column, this syntax may be used:**datatable**

` INDEX(datatable; row)`.

- If
has a single row, this syntax may be used:**datatable**

` INDEX(datatable; column)`.

### Example:

If cells ` B1`,

`,`

**B2**`contain`

**B3**`,`

**5**`and`

**red**`and cells`

**6**`,`

**C1**`,`

**C2**`contain`

**C3**`,`

**TRUE**`and`

**3**`:-`

**blue****INDEX(B1:C3; 1; 1)**

- returns
.**5**

**INDEX(B1:C3; 3; 2)**

- returns
.**blue**

**INDEX(B1:B3; 2)**

- returns
.**red**

**INDEX(B1:C2; 1; 2)**

- returns
.**TRUE**

**INDEX(B3:C3; 2)**

- returns
.**blue**

### INDEX function in array formula context

Like other functions, INDEX may be used in an array formula context. When entered as an array formula, the *row*, *column*, and *areanumber* parameters — which are expected to be scalars — can be supplied as arrays instead.

This example shows a *row* parameter supplied as a two-row vector, and the *column* and *areanumber* parameters as non-zero scalars. These parameters ask INDEX to return a reference to a single cell. The array context expands this to multiple rows with a single column.

` =INDEX({1;3;5|7;9;10};{2|1};1;1)`, entered as an array formula using

**Command-Shift-Enter**

- returns array
, with two rows and one column.**{7|1}**

Here instead, the *row* and *areanumber* parameters are non-zero scalars, and the *column* parameter is zero. These parameters ask INDEX to return a reference to all columns of one row in the datatable. The array context expands this to a result to enough cells to hold all columns of the row.

` =INDEX({1;3;5|7;9;10};2;0;1)`, entered as an array formula using

**Command-Shift-Enter**

- returns array
, with one row and three columns.**{7; 9; 10}**

**N.B.** If you both use an array parameter and ask for a complete row to be returned, then all but the first cells of the row are returned as *#VALUE!* errors instead of as the values in the datatable. This behaviour is seen in OpenOffice.org Calc version 4.0.1, and is the subject of issue 125743. It might be correct, and might be an error.

` =INDEX({1;3;5|7;9;10};{2|1};0;1)`, entered as an array formula using

**Command-Shift-Enter**

- returns array
, with two row and three columns, but errors in place of expected values.**{7; #VALUE!; #VALUE! | 1; #VALUE!; #VALUE!}**