Difference between revisions of "Documentation/How Tos/Calc: MATCH function"
From Apache OpenOffice Wiki
< Documentation | How Tos
(→Syntax:: #N/A error added) |
(→See also:) |
||
Line 42: | Line 42: | ||
'''[[Documentation/How_Tos/Calc: Spreadsheet functions|Spreadsheet functions]]''' | '''[[Documentation/How_Tos/Calc: Spreadsheet functions|Spreadsheet functions]]''' | ||
+ | |||
+ | [[Documentation/How_Tos/Calc: Functions listed alphabetically|'''Functions listed alphabetically''']], | ||
+ | [[Documentation/How_Tos/Calc: Functions listed by category|'''Functions listed by category''']] | ||
=== Issues: === | === Issues: === | ||
*<tt>'''MATCH'''</tt> cannot yet accept an array for the second parameter, as required by the forthcoming international standard ODFF ([http://qa.openoffice.org/issues/show_bug.cgi?id=8947 issue 8947]). For example <tt>'''MATCH(2;{1;2;3};0)'''</tt> returns <tt>'''Err:504'''</tt>. | *<tt>'''MATCH'''</tt> cannot yet accept an array for the second parameter, as required by the forthcoming international standard ODFF ([http://qa.openoffice.org/issues/show_bug.cgi?id=8947 issue 8947]). For example <tt>'''MATCH(2;{1;2;3};0)'''</tt> returns <tt>'''Err:504'''</tt>. |
Revision as of 12:59, 13 September 2008
MATCH
Returns the position of a search item in a single row or column table.
Syntax:
MATCH(searchitem; searchregion; matchtype)
- searchitem is the value to be found within the single row or single column range searchregion.
- If matchtype is 1 or omitted, searchregion is assumed to be sorted in ascending order. MATCH then returns the position of the largest value in searchregion that is less than or equal to searchitem.
- If matchtype is -1 or omitted, searchregion is assumed to be sorted in descending order. MATCH then returns the position of the smallest value in searchregion that is greater than or equal to searchitem.
- If matchtype is 0, MATCH returns the position of the first exact match. searchregion may be unsorted. In this case searchitem may contain a regular expression, which will be interpreted if regular expressions are selected in the Tools - Options - OpenOffice.org Calc - Calculate dialog.
- If the search fails, MATCH returns the #N/A error.
Example:
MATCH(5; B1:B4; 1)
- where cells B1, B2, B3, B4 contain 3, 4, 8 and 9, returns 2. The second value in B1:B4 is 4, which is the largest value that is less than or equal to 5.
MATCH(5; B1:B4; -1)
- where cells B1, B2, B3, B4 contain 9, 8, 4 and 3, returns 2. The second value in B1:B4 is 8, which is the smallest value that is greater than or equal to 5.
MATCH(5; B1:B4; 0)
- where cells B1, B2, B3, B4 contain 4, 7, 5 and 2, returns 3, as the value 5 is the third item in B1:B4.
MATCH("r.d"; B1:B4; 0)
- where cells B1, B2, B3, B4 contain blue, red, green and pink, returns 2 (if regular expressions are enabled). The regular expression "r.d" matches red.
See also:
CHOOSE, HLOOKUP, INDEX, LOOKUP, OFFSET, VLOOKUP
Functions listed alphabetically, Functions listed by category
Issues:
- MATCH cannot yet accept an array for the second parameter, as required by the forthcoming international standard ODFF (issue 8947). For example MATCH(2;{1;2;3};0) returns Err:504.