Documentation/Calc Functions/MATCH
TDF LibreOffice Document Liberation Project Community Blogs Weblate Nextcloud Redmine Ask LibreOffice Donate
Function name:
MATCH
Category:
Spreadsheet
Summary:
Identifies the relative position of a search item in a single-row or single-column cell range, depending on its arguments.
The matching process can also 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 either 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. 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 in which sorting order the data in Lookup array will be searched-for. The intended values for Type are as follows:
- Type =
1(default). Lookup array is assumed to be sorted in ascending order (see the #Additional details: section below for a description of the sorting order). MATCH returns the position of the largest value in Lookup array that is less than or equal to Search criterion. - Type =
0. Lookup array is not assumed to be sorted in any particular way. MATCH returns the position of the first exact match. Search criterion can optionally utilize either regular expressions or wildcards, if these are enabled. - Type =
-1. 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 and unreliable. 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 either a regular expression or a 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 error.
- If Type is non-zero and Search criterion precedes the first cell in Lookup array for the specified sorting order, then MATCH reports the #N/A error.
Additional details:
Details specific to MATCH function
- Boolean values FALSE and TRUE are treated as numbers 0 and 1 respectively.
- 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. 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 alphabetical order. 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 alphabetical order appearing before numbers in descending order. 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 ▸ ▸ ▸ (or ▸ ▸ ▸ 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 ▸ ▸ ▸ (or ▸ ▸ ▸ 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 ▸ ▸ ▸ (or ▸ ▸ ▸ on macOS) dialog, then Search criterion must match the whole text in a cell. Otherwise, it can match just part of the text. - The Case sensitive option in the General Calculations area of the ▸ ▸ ▸ (or ▸ ▸ ▸ on macOS) dialog, has no effect on the operation of MATCH.
General information about Calc's regular expressions
 |
Note:
|
- 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.
- In addition, the LibreOffice Help system provides a high-level list of 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 ▸ ▸ ▸ and check whether 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.
| 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" either, 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 ▸ ▸ ▸ , 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. |
Search exactly for the value 2 in the specified cell range, located in the fourth position. |
4 |
| =MATCH(3.6; A1:M1; 0) | 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" (without quotation marks) and number 0 respectively. |
Search exactly for "Jack" (without quotation marks, not case-sensitive) in the specified cell range, located in the ninth position. | 9 |
| =MATCH(A3; A1:M1; A4)
where cells A3 and A4 contain the string "jack" (without quotation marks) and number 0 respectively. |
Search exactly for "jack" (without quotation marks, not case-sensitive) in the specified cell range. | 9 |
| =MATCH("K*"; A1:M1; 0)
with wildcards enabled. |
Search for a value matching exactly the expression in the specified cell range, finding the string "King" in the second entry. | 2 |
| =MATCH("^Q.*"; A1:M1; 0)
with regular expressions enabled. |
Search for a value matching exactly the regular expression "^Q.*" in the specified cell range, finding the string "Queen" in the thirteenth entry.
|
13 |
| =MATCH("(?-i)^[Q]{1}[a-z]*$"; A1:M1; 0)
with regular expressions enabled. |
Search for a value matching exactly the regular expression "(?-i)^[Q]{1}[a-z]*$" in the specified cell range, requiring case-sensitivity in this case, finding the string "Queen" in the thirteenth entry.
|
13 |
| =MATCH("(?-i)^[q]{1}[a-z]*$"; A1:M1; 0)
with regular expressions enabled. |
Search for a value matching exactly the regular expression "(?-i)^[q]{1}[a-z]*$" in the specified cell range, requiring case-sensitivity in this case, not finding a match.
|
#N/A |
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 function in the following examples).
The strings are entered into their cells without quotation marks.
Compare the results with the third set of examples.
| A | |
|---|---|
| 1 | 2 |
| 2 | 3 |
| 3 | 4 |
| 4 | 5 |
| 5 | 6 |
| 6 | 7 |
| 7 | 8 |
| 8 | 9 |
| 9 | 10 |
| 10 | Ace |
| 11 | Jack |
| 12 | King |
| 13 | Queen |
| Formula | Description | Returns |
|---|---|---|
| =MATCH(1; A1:A13) |
As the Type argument is omitted, the default value of |
#N/A |
| =MATCH(2.9; A1:A13; 1) | Search for the position corresponding to the largest value in Lookup array that is less than or equal to 2.9. | 1 |
| =MATCH(23.5; A1:A13; 1) | The position corresponding to the largest value in Lookup array that is less than or equal to 23.5. | 9 |
| =MATCH("Aardvark"; A1:A13; 1) | The Search criterion argument is alphabetically before the first string in the list. | #N/A |
| =MATCH("King"; A1:A13; 1) | Search for the position of "King" in Lookup array. | 12 |
| =MATCH("Zulu"; A1:A13; 1) |
"Zulu" is not found in Lookup array. Search for the position of the value in Lookup array that resides 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 function in the following examples).
The strings are entered into their cells without quotation marks.
Compare the results with the previous set of examples.
| A | |
|---|---|
| 1 | Queen |
| 2 | King |
| 3 | Jack |
| 4 | Ace |
| 5 | 10 |
| 6 | 9 |
| 7 | 8 |
| 8 | 7 |
| 9 | 6 |
| 10 | 5 |
| 11 | 4 |
| 12 | 3 |
| 13 | 2 |
| Formula | Description | Returns |
|---|---|---|
| =MATCH("Zulu"; A1:A13; -1) | The Search criterion argument resides before the initial value in the list. | #N/A |
| =MATCH("King"; A1:A13; -1) | Search for the position of "King" in Lookup array. | 2 |
| =MATCH("Aardvark"; A1:A13; -1) |
"Aardvark" is not found in Lookup array. Search for the position of the value in Lookup array that resides immediately before where "Aardvark" would appear. |
4 |
| =MATCH(23.5; A1:A13; -1) | The Search criterion argument is greater than the initial value in the list. | #N/A |
| =MATCH(2.9; A1:A13; -1) | The position of the smallest value in Lookup array that is greater than or equal to 2.9. | 12 |
| =MATCH(1; A1:A13; -1) | The position of the smallest value in Lookup array that is greater than or equal to 1 | 13 |
Related LibreOffice functions:
ODF standard:
Related (or similar) Excel functions:
MATCH