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

From Apache OpenOffice Wiki
Jump to: navigation, search
m (Robot: Automated text replacement %s)
 
(10 intermediate revisions by 6 users not shown)
Line 1: Line 1:
__NOTOC__
+
{{Documentation/MasterTOC
 +
|bookid=1234'''
 +
|booktitle=<div style="padding: 8px; font-size: 140%; font-weight: bold; background-color: #9BC0F5;">CALC FUNCTIONS</div>
 +
|ShowParttitle=block|parttitle=[[Documentation/How_Tos/Calc:_Spreadsheet_functions|<div style="font-size: 140%;">Spreadsheet Functions]]
 +
|ShowNextPage=block|NextPage=Documentation/How_Tos/Calc:_INDIRECT_function
 +
|ShowPrevPage=block|PrevPage=Documentation/How_Tos/Calc:_HLOOKUP_function
 +
|ShowPrevPart=block|PrevPart=Documentation/How_Tos/Calc:_Number_Conversion_functions
 +
|ShowNextPart=block|NextPart=Documentation/How_Tos/Calc:_Statistical_functions
 +
|toccontent= <div style="padding: 4px; font-size: 130%; font-weight: hidden; background-color:#DCE9FC;">FUNCTIONS</div>
 +
 
 +
<div style="font-size: 140%; border-style: outset outset outset none; border-color:#DCE9FC;">Spreadsheet Lookup functions</div>
 +
* [[Documentation/How_Tos/Calc:_ADDRESS_function|<div style="font-size: 120%;">Address]]
 +
* [[Documentation/How_Tos/Calc:_CHOOSE_function|<div style="font-size: 120%;">Choose]]
 +
* [[Documentation/How_Tos/Calc:_HLOOKUP_function|<div style="font-size: 120%;">Hlookup]]
 +
* [[Documentation/How_Tos/Calc:_INDEX_function|<div style="font-size: 120%; border-style: double; border-color:#778899;">Index]]
 +
* [[Documentation/How_Tos/Calc:_INDIRECT_function|<div style="font-size: 120%;">Indirect]]
 +
* [[Documentation/How_Tos/Calc:_LOOKUP_function|<div style="font-size: 120%;">Lookup]]
 +
* [[Documentation/How_Tos/Calc:_MATCH_function|<div style="font-size: 120%;">Math]]
 +
* [[Documentation/How_Tos/Calc:_OFFSET_function|<div style="font-size: 120%;">Offset]]
 +
* [[Documentation/How_Tos/Calc:_VLOOKUP_function|<div style="font-size: 120%;">Vlookup]]
 +
 
 +
<div style="font-size: 140%; border-style: outset outset outset none; border-color:#DCE9FC;">Spreadsheet Information functions</div>
 +
* [[Documentation/How_Tos/Calc:_AREAS_function|<div style="font-size: 120%;">Areas]]
 +
* [[Documentation/How_Tos/Calc:_COLUMN_function|<div style="font-size: 120%;">Column]]
 +
* [[Documentation/How_Tos/Calc:_COLUMNS_function|<div style="font-size: 120%;">Columns]]
 +
* [[Documentation/How_Tos/Calc:_ERRORTYPE_function|<div style="font-size: 120%;">Errortype]]
 +
* [[Documentation/How_Tos/Calc:_INFO_function|<div style="font-size: 120%;">Info]]
 +
* [[Documentation/How_Tos/Calc:_ROW_function|<div style="font-size: 120%;">Row]]
 +
* [[Documentation/How_Tos/Calc:_ROWS_function|<div style="font-size: 120%;">Rows]]
 +
* [[Documentation/How_Tos/Calc:_SHEET_function|<div style="font-size: 120%;">Sheet]]
 +
* [[Documentation/How_Tos/Calc:_SHEETS_function|<div style="font-size: 120%;">Sheets]]
 +
 
 +
<div style="font-size: 140%; border-style: outset outset outset none; border-color:#DCE9FC;">Other functions</div>
 +
* [[Documentation/How_Tos/Calc:_DDE_function|<div style="font-size: 120%;">Dde]]
 +
* [[Documentation/How_Tos/Calc:_HYPERLINK_function|<div style="font-size: 120%;">Hyperlink]]
 +
* [[Documentation/How_Tos/Calc:_STYLE_function|<div style="font-size: 120%;">Style]]
 +
}}__TOC__
  
 
== INDEX ==
 
== INDEX ==
Line 12: Line 48:
  
 
: 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:
<tt>'''INDEX(datatable; row)'''</tt>
+
<tt>'''INDEX(datatable; row)'''</tt>.
 +
 
 +
: If <tt>'''datatable'''</tt> has a single row, this syntax may be used:
 +
<tt>'''INDEX(datatable; column)'''</tt>.
  
 
=== Example: ===
 
=== Example: ===
Line 26: Line 65:
 
: returns <tt>'''red'''</tt>.
 
: returns <tt>'''red'''</tt>.
  
<tt>'''INDEX(B1:C2 1; 2)'''</tt>
+
<tt>'''INDEX(B1:C2; 1; 2)'''</tt>
 
: returns <tt>'''TRUE'''</tt>.
 
: returns <tt>'''TRUE'''</tt>.
  
{{Documentation/SeeAlso|
+
<tt>'''INDEX(B3:C3; 2)'''</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.
 +
 
 +
 
 +
{{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]]
Line 41: Line 103:
 
* [[Documentation/How_Tos/Calc: Functions listed alphabetically|Functions listed alphabetically]]
 
* [[Documentation/How_Tos/Calc: Functions listed alphabetically|Functions listed alphabetically]]
 
* [[Documentation/How_Tos/Calc: Functions listed by category|Functions listed by category]]}}
 
* [[Documentation/How_Tos/Calc: Functions listed by category|Functions listed by category]]}}
 
+
[[Category: Documentation/Reference/Calc/Spreadsheet functions]]
=== Issues: ===
+
*Calc cannot yet handle empty parameters, so for example <tt>'''INDEX(multirange; 3; ; 2)'''</tt> will fail.
+
*If <tt>'''datatable'''</tt> has a single row, the ODFF standard implies that the syntax <tt>'''INDEX(datatable; column)'''</tt> may be used. This is to be implemented in Calc (issue 78781)
+

Latest revision as of 08:45, 17 July 2018

CALC FUNCTIONS
FUNCTIONS
Spreadsheet Lookup functions
Spreadsheet Information functions
Other 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 Command-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 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 {7; #VALUE!; #VALUE! | 1; #VALUE!; #VALUE!}, with two row and three columns, but errors in place of expected values.




See Also
Retrieved from "https://wiki.openoffice.org/w/index.php?title=Documentation/How_Tos/Calc:_INDEX_function&oldid=243515"
Views
Personal tools
Navigation
Tools