Difference between revisions of "Documentation/How Tos/Calc: MATCH function"
From Apache OpenOffice Wiki
< Documentation | How Tos
OOoWikiBot (Talk | contribs) m (Robot: Automated text replacement %s) |
m |
||
Line 48: | Line 48: | ||
* [[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]] | + | [[Category: Documentation/Reference/Calc/Spreadsheet functions]] |
Revision as of 11:29, 17 May 2010
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, 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.
Issues:
- In releases before 3.0, MATCH cannot 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. This form is supported by OpenOffice.org Calc versions 3.0 and later, giving the correct result: 2.