Documentation/Calc Functions/MATCH

From The Document Foundation Wiki
Jump to navigation Jump to search
This page contains changes which are not marked for translation.
Other languages:
English • ‎Nederlands



Function name:

MATCH

Category:

Spreadsheet

Summary:

Determines the position of a search item in a single row or single column cell range.

The matching process can utilize either wildcards or regular expressions. By default, the matching is not case-sensitive, but a case-sensitive match can be carried out when using a regular expression.

Syntax:

MATCH(Search criterion; Lookup array[; Type])

Returns:

Returns an integer ranging from 1 to the size of the specified lookup array, giving the position of the searched for item in the given range of cells.

Arguments:

Search criterion is a text string (in quotation marks), a number, a Boolean, or a reference to a cell containing one of those types. Search criterion is matched against the contents of cells in Lookup array.

Lookup array is the single row or single column range of cells to be searched, typically defined by entering the cell reference for the upper left-hand cell, followed by a colon (:), and then the lower right-hand cell reference (for example, A1:E1 or B1:B7). It may also be specified using the name of a named range or a database range, or may be passed as an inline array.

Type is an integer, or a reference to a cell containing an integer, that indicates how the data in Lookup array is organized. The intended values for Type are as follows:

  • Type = 1. In this case, Lookup array is assumed to be sorted in ascending order (see the #Additional details: section below for a description of the sort order). MATCH returns the position of the largest value in Lookup array that is less than or equal to Search criterion. This is the default value applied when the Type is omitted.
  • Type = 0. In this case, Lookup array is assumed to be unsorted. MATCH returns the position of the first exact match. Search criterion can utilize regular expressions or wildcards if these are enabled.
  • Type = -1. In this case, Lookup array is assumed to be sorted in descending order. MATCH returns the position of the smallest value in Lookup array that is greater than or equal to Search criterion.

The following conditions may arise:

  • If Lookup array is a multi-dimensional cell range, then MATCH reports a parameter list error (Err:504).
  • If Type is non-numeric, then MATCH reports a #VALUE! error.
  • If Type is any positive number, then MATCH assumes that Lookup array is sorted in ascending order.
  • If Type is any negative number, then MATCH assumes that Lookup array is sorted in descending order.
  • If Type is non-zero, but the cells in Lookup array are not ordered as expected, then the result returned by MATCH is unpredictable. In such cases, it is possible that no error will be reported and it may not be obvious that the returned result is meaningless.
  • If Type is non-zero and Search criterion utilizes a regular expression or wildcard expression, MATCH does not routinely report an error. However it may not be obvious when the returned result is meaningless.
  • If Type is zero and no exact match to Search criterion can be found in Lookup array, then MATCH reports the #N/A (value not available) error.
  • If Type is non-zero and Search criterion precedes the first cell in Lookup array for the specified sort order, then MATCH reports the #N/A (value not available) error.

Additional details:

Details specific to MATCH function

  • If Type is zero and Lookup array contains multiple cells with the same content, MATCH will only match one of the cells sharing common content and this could be a cause of confusion.
  • If Type is greater than zero, Lookup array should be sorted with numbers in ascending order appearing before text values in alphabetic order. Boolean values FALSE and TRUE are treated as numbers 0 and 1 respectively. MATCH calculates where in the Lookup array the value of Search criterion would appear in the sorted order.
  • If Type is less than zero, Lookup array should be sorted with text values in descending alphabetic order appearing before numbers in descending order. Boolean values FALSE and TRUE are treated as numbers 0 and 1 respectively. MATCH calculates where in the Lookup array the value of Search criterion would appear in the sorted order.
  • If regular expressions are enabled in the Formulas Wildcards area of the Tools ▸ Options ▸ LibreOffice Calc ▸ Calculate (or LibreOffice ▸ Preferences ▸ LibreOffice Calc ▸ Calculate on macOS) dialog, then MATCH will find exact matches treating Search criterion as a regular expression.
  • If wildcards are enabled in the Formulas Wildcards area of the Tools ▸ Options ▸ LibreOffice Calc ▸ Calculate (or LibreOffice ▸ Preferences ▸ LibreOffice Calc ▸ Calculate on macOS) dialog, then MATCH will find exact matches treating Search criterion as a wildcard expression.
  • If the Search criteria = and <> must apply to whole cells option is ticked in the General Calculations area of the Tools ▸ Options ▸ LibreOffice Calc ▸ Calculate (or LibreOffice ▸ Preferences ▸ LibreOffice Calc ▸ Calculate on macOS) dialog, then Search criterion must match the whole text in a cell; if not, it can match just part of the text.
  • The Case sensitive option in the General Calculations area of the Tools ▸ Options ▸ LibreOffice Calc ▸ Calculate (or LibreOffice ▸ Preferences ▸ LibreOffice Calc ▸ Calculate on macOS) dialog has no effect on the operation of MATCH.


General information about Calc's regular expressions

Note pin.svg

Note:
For convenience, the information in this subsection is repeated on all pages describing functions that manipulate regular expressions.

  • A regular expression is a string of characters defining a pattern of text that is to be matched. More detailed, general background information can be found on Wikipedia’s Regular expression page.
  • Regular expressions are widely used in many domains and there are multiple regular expression processors available. Calc utilises the open source Regular Expressions package from the International Components for Unicode (ICU), see their Regular Expressions documentation for further details, including a full definition of the syntax for ICU Regular Expressions.
  • Calc’s regular expression engine supports numbered capture groups, which allow sub-ranges within a match to be identified and used within replacement text. Parentheses are used to group components of a regular expression together and create a numbered capture group. To insert a capture group into a replacement text, use the "$n" form, where n is the number of the capture group.


General information about Calc's wildcards

Wildcards are special characters that can be used in search strings passed as arguments to some Calc functions; they can also be used to define search criteria in the Find & Replace dialog. The use of wildcards enables the definition of more advanced search parameters with a single search string.

Calc supports either wildcards or regular expressions as arguments depending on the current application settings. By default, wildcards are supported instead of regular expressions.

To make sure wildcards are supported, go to Tools ▸ Options ▸ LibreOffice Calc ▸ Calculate and check if the option Enable wildcards in formulas is selected. Note that you can use this dialog to switch to regular expressions by choosing Enable regular expressions in formulas or choose to support neither wildcards nor regular expressions.

The following table identifies the wildcards that Calc supports.

Calc wildcards
Wildcard Description
? (question mark) Matches any single character. For example, the search string "b?g" matches "bag" and "beg" but will not match "boog" or "mug".

Note that it will not match "bg" as well, since "?" must match exactly one character. The "?" wildcard does not correspond to a zero-character match.
* (asterisk) Matches any sequence of characters, including an empty string. For example, the search string "*cast" will match "cast", "forecast", and “outcast”, but will not match "forecaster" using default Calc settings.

If the option Search criteria = and <> must apply to whole cells is disabled in Tools > Options > LibreOffice Calc > Calculate, then "forecaster" will be a match using the "*cast" search string.
~ (tilde) Escapes the special meaning of a question mark, asterisk, or tilde character that follows immediately after the tilde character.

For example, the search string "why~?" matches "why?" but will not match "whys" nor "why~s".


Wildcard comparisons are not case sensitive, hence "A?" will match both "A1" and "a1".

These wildcards are supported in both Calc and Microsoft Excel. Therefore, if interoperability between the two applications is needed, choose to work with wildcards instead of regular expressions. Conversely, if interoperability is not necessary, consider using regular expressions for more powerful search capabilities.


Examples:

In the first set of examples, it is assumed that cells A1:M1 contain the following numbers and strings: 7, "King", 3, 2, 8, 5, "Ace", 6, "Jack", 10, 4, 9, "Queen" (representing the 13 cards in one suit of a pack of playing cards, shuffled into a random order). The strings are entered into their cells without quotation marks.

Formula Description Returns
=MATCH(2; lookup; 0) where lookup is a named range (or database range) covering the cell range A1:M1. Here the function searches the unordered values in the specified cell range and finds the value 2 in the fourth entry. 4
=MATCH(3.6; A1:M1; 0) Here the function returns the #N/A error since there is no exact match for the value 3.6. #N/A
=MATCH(A3; A1:M1; A4) where cells A3 and A4 contain the string "jack" (entered without quotation marks) and number 0 respectively. Here the function searches the unordered values in the specified cell range and finds the string "Jack" in the ninth entry. 9
=MATCH("^Q.*"; A1:M1; 0) with regular expressions enabled. Here the function searches the unordered values in the specified cell range and matches the string "Queen" in the thirteenth entry. 13
=MATCH("K*"; A1:M1; 0) with wildcards enabled. Here the function searches the unordered values in the specified cell range and matches the string "King" in the second entry. 2

In the second set of examples, it is assumed that cells A1:A13 contain the following numbers and strings: 2, 3, 4, 5, 6, 7, 8, 9, 10, "Ace", "Jack", "King", "Queen" (representing the 13 cards in one suit of a pack of playing cards, placed in the ascending order required by the MATCH functions). The strings are entered into their cells without quotation marks.

Formula Description Returns
=MATCH(1; A1:A13) As the Type argument is omitted, the default value of 1 is assumed. Here the function returns the #N/A error because the Search criterion argument is less than the initial value in the list. #N/A
=MATCH(2.9; A1:A13; 1) Here the function returns the position of the largest value in Lookup array that is less than or equal to 2.9. 1
=MATCH(23.5; A1:A13; 1) Here the function returns the position of the largest value in Lookup array that is less than or equal to 23.5. 9
=MATCH("Aardvark"; A1:A13; 1) Here the function returns the #N/A error because the Search criterion argument is alphabetically before the first string in the list. #N/A
=MATCH("King"; A1:A13; 1) Here the function returns the position of "King" in Lookup array. 12
=MATCH("Zulu"; A1:A13; 1) Here the function returns the position of the value in Lookup array that lies immediately before where "Zulu" would appear. 13

In the third set of examples, it is assumed that cells A1:A13 contain the following numbers and strings: "Queen", "King", "Jack", "Ace", 10, 9, 8, 7, 6, 5, 4, 3, 2 (representing the 13 cards in one suit of a pack of playing cards, placed in the descending order required by the MATCH functions). The strings are entered into their cells without quotation marks.

Formula Description Returns
=MATCH("Zulu"; A1:A13; -1) Here the function returns the #N/A error because the Search criterion argument lies before the initial value in the list. #N/A
=MATCH("King"; A1:A13; -1) Here the function returns the position of "King" in Lookup array. 2
=MATCH("Aardvark"; A1:A13; -1) Here the function returns the position of the value in Lookup array that lies immediately before where "Aardvark" would appear. 4
=MATCH(23.5; A1:A13; -1) Here the function returns the #N/A error because the Search criterion argument is greater than the initial value in the list. #N/A
=MATCH(2.9; A1:A13; -1) Here the function returns the position of the smallest value in Lookup array that is greater than or equal to 2.9. 12
=MATCH(1; A1:A13; -1) Here the function returns the position of the smallest value in Lookup array that is greater than or equal to 1 13

Related LibreOffice functions:

HLOOKUP

INDEX

LOOKUP

OFFSET

VLOOKUP

ODF standard:

Section 6.14.9, part 2

Equivalent Excel functions:

MATCH