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) |
|||
| Line 14: | Line 14: | ||
: If <tt>'''matchtype'''</tt> is <tt>'''-1'''</tt> or omitted, <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>'''-1'''</tt> or omitted, <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]] | + | : 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 the search fails, <tt>'''MATCH'''</tt> returns the <tt>'''#N/A'''</tt> error. | : If the search fails, <tt>'''MATCH'''</tt> returns the <tt>'''#N/A'''</tt> error. | ||
| Line 37: | Line 37: | ||
* [[Documentation/How_Tos/Calc: LOOKUP function|LOOKUP]] | * [[Documentation/How_Tos/Calc: LOOKUP function|LOOKUP]] | ||
* [[Documentation/How_Tos/Calc: OFFSET function|OFFSET]] | * [[Documentation/How_Tos/Calc: OFFSET function|OFFSET]] | ||
| − | * [[Documentation/How_Tos/Calc: VLOOKUP function|VLOOKUP]] | + | * [[Documentation/How_Tos/Calc: VLOOKUP function|VLOOKUP]] |
| − | * [[Documentation/How_Tos/Regular Expressions in Calc|Regular Expressions in Calc]] | + | * [[Documentation/How_Tos/Regular Expressions in Calc|Regular Expressions in Calc]] |
| − | * [[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 alphabetically|Functions listed alphabetically]] | ||
Revision as of 14:21, 26 February 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.
Template:Documentation/SeeAlso
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.
