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 |
||
| (11 intermediate revisions by 7 users not shown) | |||
| Line 1: | Line 1: | ||
| − | __NOTOC__ | + | {{DISPLAYTITLE:MATH function}} |
| + | {{Documentation/CalcFunc SpreadsheetTOC | ||
| + | |ShowPrevNext=block | ||
| + | |PrevPage=Documentation/How_Tos/Calc:_LOOKUP_function | ||
| + | |NextPage=Documentation/How_Tos/Calc:_OFFSET_function | ||
| + | }}__NOTOC__ | ||
== MATCH == | == MATCH == | ||
| Line 12: | Line 17: | ||
: 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 | + | : 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 31: | Line 36: | ||
: 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: === |
| + | *If the <tt>'''searchregion'''</tt> contains mixed data (strings and numbers) and the <tt>'''searchitem'''</tt> is text, then the returned value is the number that is found, if it is found before the <tt>'''searchitem'''</tt>. This of course is wrong. Excel (at least some versions) apparently ignores the numbers in the <tt>'''searchregion'''</tt>. | ||
| + | |||
| + | {{SeeAlso|EN| | ||
* [[Documentation/How_Tos/Calc: CHOOSE function|CHOOSE]] | * [[Documentation/How_Tos/Calc: CHOOSE function|CHOOSE]] | ||
* [[Documentation/How_Tos/Calc: HLOOKUP function|HLOOKUP]] | * [[Documentation/How_Tos/Calc: HLOOKUP function|HLOOKUP]] | ||
| Line 37: | Line 45: | ||
* [[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]] | ||
* [[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/Spreadsheet functions]] | |
| − | + | ||
| − | + | ||
Latest revision as of 11:13, 2 February 2024
- Spreadsheet FunctionsSpreadsheet Lookup functions
- ADDRESSCHOOSEHLOOKUPINDEXINDIRECTLOOKUPMATHOFFSETVLOOKUPSpreadsheet Information functions
- AREASCOLUMNCOLUMNSERRORTYPEINFOROWROWSSHEETSHEETSOther functions
-
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:
- If the searchregion contains mixed data (strings and numbers) and the searchitem is text, then the returned value is the number that is found, if it is found before the searchitem. This of course is wrong. Excel (at least some versions) apparently ignores the numbers in the searchregion.
See Also
-
