# INDEX function

## 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 Contrl + ⇧ 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 behavior is seen in OpenOffice 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 Contrl + ⇧ Shift + ↵ Enter

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

**See Also**