Difference between revisions of "Documentation/How Tos/Calc: INDEX function"
From Apache OpenOffice Wiki
< Documentation | How Tos
Javier Lopez (Talk | contribs) |
m |
||
| (3 intermediate revisions by 3 users not shown) | |||
| Line 1: | Line 1: | ||
| − | {{ | + | {{DISPLAYTITLE:INDEX function}} |
| − | + | {{Documentation/CalcFunc SpreadsheetTOC | |
| − | + | |ShowPrevNext=block | |
| − | + | |PrevPage=Documentation/How_Tos/Calc:_HLOOKUP_function | |
| − | | | + | |NextPage=Documentation/How_Tos/Calc:_INDIRECT_function |
| − | + | }}__NOTOC__ | |
| − | | | + | |
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | }} | + | |
== INDEX == | == INDEX == | ||
| Line 45: | Line 14: | ||
: returns the value at position <tt>'''row'''</tt>, <tt>'''column'''</tt> in the range or array <tt>'''datatable'''</tt>. | : returns the value at position <tt>'''row'''</tt>, <tt>'''column'''</tt> in the range or array <tt>'''datatable'''</tt>. | ||
| − | : If <tt>'''datatable'''</tt> has multiple ranges then <tt>'''areanumber'''</tt> specifies which is to be used. <tt>'''areanumber'''</tt> is optional and defaults to <tt>'''1'''</tt>. | + | : If <tt>'''datatable'''</tt> has multiple ranges, then <tt>'''areanumber'''</tt> specifies which is to be used. <tt>'''areanumber'''</tt> is optional and defaults to <tt>'''1'''</tt>. |
: If <tt>'''datatable'''</tt> has a single column, this syntax may be used: | : If <tt>'''datatable'''</tt> has a single column, this syntax may be used: | ||
| Line 71: | Line 40: | ||
: 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 {{key|Contrl|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 behavior is seen in OpenOffice Calc version 4.0.1, and is the subject of {{bug|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 {{key|Contrl|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. | ||
| + | |||
| + | |||
| + | {{SeeAlso|EN| | ||
* [[Documentation/How_Tos/Calc: CHOOSE function|CHOOSE]] | * [[Documentation/How_Tos/Calc: CHOOSE function|CHOOSE]] | ||
* [[Documentation/How_Tos/Calc: HLOOKUP function|HLOOKUP]] | * [[Documentation/How_Tos/Calc: HLOOKUP function|HLOOKUP]] | ||
Latest revision as of 11:03, 2 February 2024
- Spreadsheet FunctionsSpreadsheet Lookup functions
- ADDRESSCHOOSEHLOOKUPINDEXINDIRECTLOOKUPMATHOFFSETVLOOKUPSpreadsheet Information functions
- AREASCOLUMNCOLUMNSERRORTYPEINFOROWROWSSHEETSHEETSOther functions
-
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, column in the range or array datatable.
- If datatable has multiple ranges, then areanumber specifies which is to be used. areanumber is optional and defaults to 1.
- If datatable has a single column, this syntax may be used:
INDEX(datatable; row).
- If datatable has a single row, this syntax may be used:
INDEX(datatable; column).
Example:
If cells B1, B2, B3 contain 5, red and 6 and cells C1, C2, C3 contain TRUE, 3 and 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 {7|1}, 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.
=INDEX({1;3;5|7;9;10};2;0;1), entered as an array formula using Contrl + ⇧ Shift + ↵ Enter
- returns array {7; 9; 10}, 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 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 {7; #VALUE!; #VALUE! | 1; #VALUE!; #VALUE!}, with two row and three columns, but errors in place of expected values.
See Also
-
