Difference between revisions of "Documentation/How Tos/Calc: MATCH function"
From Apache OpenOffice Wiki
< Documentation | How Tos
m (→Issues:) |
|||
| (13 intermediate revisions by 7 users not shown) | |||
| Line 1: | Line 1: | ||
| − | + | {{Documentation/MasterTOC | |
| + | |bookid=1234''' | ||
| + | |booktitle=<div style="padding: 8px; font-size: 140%; font-weight: bold; background-color: #9BC0F5;">CALC FUNCTIONS</div> | ||
| + | |ShowParttitle=block|parttitle=[[Documentation/How_Tos/Calc:_Spreadsheet_functions|<div style="font-size: 140%;">Spreadsheet Functions]] | ||
| + | |ShowNextPage=block|NextPage=Documentation/How_Tos/Calc:_OFFSET_function | ||
| + | |ShowPrevPage=block|PrevPage=Documentation/How_Tos/Calc:_LOOKUP_function | ||
| + | |ShowPrevPart=block|PrevPart=Documentation/How_Tos/Calc:_Number_Conversion_functions | ||
| + | |ShowNextPart=block|NextPart=Documentation/How_Tos/Calc:_Statistical_functions | ||
| + | |toccontent= <div style="padding: 4px; font-size: 130%; font-weight: hidden; background-color:#DCE9FC;">FUNCTIONS</div> | ||
| + | |||
| + | <div style="font-size: 140%; border-style: outset outset outset none; border-color:#DCE9FC;">Spreadsheet Lookup functions</div> | ||
| + | * [[Documentation/How_Tos/Calc:_ADDRESS_function|<div style="font-size: 120%;">Address]] | ||
| + | * [[Documentation/How_Tos/Calc:_CHOOSE_function|<div style="font-size: 120%;">Choose]] | ||
| + | * [[Documentation/How_Tos/Calc:_HLOOKUP_function|<div style="font-size: 120%;">Hlookup]] | ||
| + | * [[Documentation/How_Tos/Calc:_INDEX_function|<div style="font-size: 120%;">Index]] | ||
| + | * [[Documentation/How_Tos/Calc:_INDIRECT_function|<div style="font-size: 120%;">Indirect]] | ||
| + | * [[Documentation/How_Tos/Calc:_LOOKUP_function|<div style="font-size: 120%;">Lookup]] | ||
| + | * [[Documentation/How_Tos/Calc:_MATCH_function|<div style="font-size: 120%; border-style: double; border-color:#778899;">Math]] | ||
| + | * [[Documentation/How_Tos/Calc:_OFFSET_function|<div style="font-size: 120%;">Offset]] | ||
| + | * [[Documentation/How_Tos/Calc:_VLOOKUP_function|<div style="font-size: 120%;">Vlookup]] | ||
| + | |||
| + | <div style="font-size: 140%; border-style: outset outset outset none; border-color:#DCE9FC;">Spreadsheet Information functions</div> | ||
| + | * [[Documentation/How_Tos/Calc:_AREAS_function|<div style="font-size: 120%;">Areas]] | ||
| + | * [[Documentation/How_Tos/Calc:_COLUMN_function|<div style="font-size: 120%;">Column]] | ||
| + | * [[Documentation/How_Tos/Calc:_COLUMNS_function|<div style="font-size: 120%;">Columns]] | ||
| + | * [[Documentation/How_Tos/Calc:_ERRORTYPE_function|<div style="font-size: 120%;">Errortype]] | ||
| + | * [[Documentation/How_Tos/Calc:_INFO_function|<div style="font-size: 120%;">Info]] | ||
| + | * [[Documentation/How_Tos/Calc:_ROW_function|<div style="font-size: 120%;">Row]] | ||
| + | * [[Documentation/How_Tos/Calc:_ROWS_function|<div style="font-size: 120%;">Rows]] | ||
| + | * [[Documentation/How_Tos/Calc:_SHEET_function|<div style="font-size: 120%;">Sheet]] | ||
| + | * [[Documentation/How_Tos/Calc:_SHEETS_function|<div style="font-size: 120%;">Sheets]] | ||
| + | |||
| + | <div style="font-size: 140%; border-style: outset outset outset none; border-color:#DCE9FC;">Other functions</div> | ||
| + | * [[Documentation/How_Tos/Calc:_DDE_function|<div style="font-size: 120%;">Dde]] | ||
| + | * [[Documentation/How_Tos/Calc:_HYPERLINK_function|<div style="font-size: 120%;">Hyperlink]] | ||
| + | * [[Documentation/How_Tos/Calc:_STYLE_function|<div style="font-size: 120%;">Style]] | ||
| + | }}__TOC__ | ||
== MATCH == | == MATCH == | ||
| Line 12: | Line 48: | ||
: 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]] | + | : 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. | ||
=== Example: === | === Example: === | ||
| Line 29: | Line 67: | ||
: 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: === |
| − | '''[ | + | *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>. |
| − | ''' | + | *If the <tt>'''searchregion'''</tt> contains mixed data (strings and numbers) and the <tt>'''searchitem'''</tt> is text, then the returned vallue 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: HLOOKUP function|HLOOKUP]] | ||
| + | * [[Documentation/How_Tos/Calc: INDEX function|INDEX]] | ||
| + | * [[Documentation/How_Tos/Calc: LOOKUP function|LOOKUP]] | ||
| + | * [[Documentation/How_Tos/Calc: OFFSET function|OFFSET]] | ||
| + | * [[Documentation/How_Tos/Calc: VLOOKUP function|VLOOKUP]] | ||
| − | + | * [[Documentation/How_Tos/Regular Expressions in Calc|Regular Expressions in Calc]] | |
| − | + | * [[Documentation/How_Tos/Calc: Spreadsheet functions|Spreadsheet functions]] | |
| − | * | + | |
| + | * [[Documentation/How_Tos/Calc: Functions listed alphabetically|Functions listed alphabetically]] | ||
| + | * [[Documentation/How_Tos/Calc: Functions listed by category|Functions listed by category]]}} | ||
| + | [[Category: Documentation/Reference/Calc/Spreadsheet functions]] | ||
Revision as of 08:46, 17 July 2018
CALC FUNCTIONS
- Spreadsheet FunctionsSpreadsheet Lookup functions
- AddressChooseHlookupIndexIndirectLookupMathOffsetVlookupSpreadsheet Information functions
- AreasColumnColumnsErrortypeInfoRowRowsSheetSheetsOther functions
-
Contents
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.
- If the searchregion contains mixed data (strings and numbers) and the searchitem is text, then the returned vallue 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
-
