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

From Apache OpenOffice Wiki
Jump to: navigation, search
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]]}}
 
=== 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>.
 

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.

Template:Documentation/SeeAlso

Personal tools