Difference between revisions of "Documentation/How Tos/Calc: MATCH function"
From Apache OpenOffice Wiki
< Documentation | How Tos
Line 12: | Line 12: | ||
: If <tt>'''matchtype'''</tt> is <tt>'''1'''</tt> or omitted, <tt>'''searchregion'''</tt> is assumed to be sorted in ascending order. <tt>'''MATCH'''</tt> then returns the position of the largest value in <tt>'''searchregion'''</tt> that is less than or equal to <tt>'''searchitem'''</tt>. | : If <tt>'''matchtype'''</tt> is <tt>'''1'''</tt> or omitted, <tt>'''searchregion'''</tt> is assumed to be sorted in ascending order. <tt>'''MATCH'''</tt> then returns the position of the largest value in <tt>'''searchregion'''</tt> that is less than or equal to <tt>'''searchitem'''</tt>. | ||
− | : If <tt>'''matchtype'''</tt> is <tt>'''-1'''</tt> | + | : If <tt>'''matchtype'''</tt> is <tt>'''-1'''</tt>, <tt>'''searchregion'''</tt> is assumed to be sorted in descending order. <tt>'''MATCH'''</tt> then returns the position of the smallest value in <tt>'''searchregion'''</tt> that is greater than or equal to <tt>'''searchitem'''</tt>. |
: If <tt>'''matchtype'''</tt> is <tt>'''0'''</tt>, <tt>'''MATCH'''</tt> returns the position of the first exact match. <tt>'''searchregion'''</tt> may be unsorted. In this case <tt>'''searchitem'''</tt> may contain a [[Documentation/How_Tos/Regular Expressions in Calc|regular expression]] which will be interpreted if regular expressions are selected in the '''Tools - Options - OpenOffice.org Calc - Calculate''' dialog. | : If <tt>'''matchtype'''</tt> is <tt>'''0'''</tt>, <tt>'''MATCH'''</tt> returns the position of the first exact match. <tt>'''searchregion'''</tt> may be unsorted. In this case <tt>'''searchitem'''</tt> may contain a [[Documentation/How_Tos/Regular Expressions in Calc|regular expression]] which will be interpreted if regular expressions are selected in the '''Tools - Options - OpenOffice.org Calc - Calculate''' dialog. | ||
Line 32: | Line 32: | ||
=== Issues: === | === Issues: === | ||
− | *In releases before 3.0, <tt>'''MATCH'''</tt> cannot 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>. This form | + | *In releases before 3.0, <tt>'''MATCH'''</tt> cannot 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>. This form <u>is</u> supported by OpenOffice.org Calc versions 3.0 and later, giving the correct result: <tt>'''2'''</tt>. |
{{Documentation/SeeAlso| | {{Documentation/SeeAlso| |
Revision as of 15:35, 1 May 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, 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.