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 30: | Line 30: | ||
<tt>'''MATCH("r.d"; B1:B4; 0)'''</tt> | <tt>'''MATCH("r.d"; B1:B4; 0)'''</tt> | ||
: where cells <tt>'''B1'''</tt>, <tt>'''B2'''</tt>, <tt>'''B3'''</tt>, <tt>'''B4'''</tt> contain <tt>'''blue'''</tt>, <tt>'''red'''</tt>, <tt>'''green'''</tt> and <tt>'''pink'''</tt>, returns <tt>'''2'''</tt> (if regular expressions are enabled). The regular expression "<tt>'''r.d'''</tt>" matches <tt>'''red'''</tt>. | : where cells <tt>'''B1'''</tt>, <tt>'''B2'''</tt>, <tt>'''B3'''</tt>, <tt>'''B4'''</tt> contain <tt>'''blue'''</tt>, <tt>'''red'''</tt>, <tt>'''green'''</tt> and <tt>'''pink'''</tt>, returns <tt>'''2'''</tt> (if regular expressions are enabled). The regular expression "<tt>'''r.d'''</tt>" matches <tt>'''red'''</tt>. | ||
+ | |||
+ | === 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>. | ||
{{Documentation/SeeAlso| | {{Documentation/SeeAlso| | ||
Line 45: | 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]]}} | ||
− | |||
− | |||
− |
Revision as of 16:11, 2 March 2009
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.
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.