Documentation/Calc Functions/MATCH
TDF LibreOffice Document Liberation Project Community Blogs Weblate Nextcloud Redmine Ask LibreOffice Donate
Function name:
MATCH
Category:
Spreadsheet
Summary:
The function gives the relative position of an item in an array that matches a specified value. The function returns the position of the value found in the lookup_array as a number.
Syntax:
MATCH(SearchCriterion; LookupArray[; 'Type'])
Returns:
Returns a positive whole number ranging from 1 which is the position of the SearchCriterion in the LookupArray which can be in ascending or descending order as mentioned by the 'Type' argument.
Arguments:
SearchCriterion is the value that is to be searched for in the single-row or single-column array. It is entered either directly or as a reference to a cell containing that value.
LookupArray is the reference searched. A lookup array can be a single row or column, or part of a single row or column.
Type may take the values 1, 0, or -1. If Type = 1 or if this optional parameter is missing, it is assumed that the first column of the search array is sorted in ascending order. If Type = -1 it is assumed that the column is sorted in descending order. This corresponds to the same function in Microsoft Excel.
If Type = 0, only exact matches are found. If the search criterion is found more than once, the function returns the index of the first matching value. Only if Type = 0 can you search for regular expressions (if enabled in calculation options) or wildcards (if enabled in calculation options).
If Type = 1 or the third parameter is missing, the index of the last value that is smaller or equal to the search criterion is returned. This applies even when the search array is not sorted. For Type = -1, the first value that is larger or equal is returned.
- If SearchCriterion is of type Text and the value found is of type Number, the #N/A Error is returned.
- For Type = 0, if no value is equal to SearchCriterion exists, the #N/A Error is returned.
Additional details:
- For Text the comparison is case-insensitive.
- Sorted ascending includes smaller Text values before larger ones (e.g., "A" before "B", and "B" before "BA"), and False before True.
- If the types are mixed, Numbers are sorted before Text, and Text before Logicals. Evaluators without a separate Logical type may include a Logical as a Number.
- The values returned may vary depending upon the HOST-USE-REGULAR-EXPRESSIONS or HOST-USE-WILDCARDS or HOST-SEARCH-CRITERIA-MUST-APPLY-TO-WHOLE-CELL properties. You can know more about it in Host-Defined Behaviors.
- The search supports wildcards or regular expressions. With regular expressions enabled, you can enter "all.*", for example to find the first location of "all" followed by any characters. If you want to search for a text that is also a regular expression, you must either precede every character with a "\" character, or enclose the text into \Q...\E. You can switch the automatic evaluation of wildcards or regular expression on and off in Tools - Options - LibreOffice Calc - Calculate.
Examples:
A | B | |
---|---|---|
1 | Employee | Salary |
2 | Hans | 21682 |
3 | Brigitte | 25250 |
4 | Ute | 46782 |
5 | Fritz | 54806 |
Formula | Description | Returns |
---|---|---|
=MATCH(25250;B2:B5) | As soon as this value is reached, the number of the row in which it was found is returned. Here B2 will be the first row and result is in B3. | 2 |
=MATCH(50000;B2:B5) | There is no exact match for 50000 so the function looks for a value just smaller than 50000 and the number of the row in which it was found is returned. Here B4's row index will be returned. | 3 |
=MATCH(50000;B2:B5,1) | There is no exact match for 50000 so the function looks for a value just smaller than 50000 and the number of the row in which it was found is returned. Here B4's row index will be returned. Passing Type as 1 indicates that LookupArray is in ascending order. | 3 |
=MATCH(50000;B2:B5,0) | There is no exact match for 50000 and function has Type = 0 so function returns #N/A error. | #N/A |
=MATCH(25250;B2:B5,-1) | The LookupArray is in ascending order, Type is -1 and but since there is an exact match so the function returns as row index for the found value. | 2 |
=MATCH(50000;B2:B5,-1) | The LookupArray is in ascending order, Type is -1 and there is no exact match hence the function returns an #N/A error. | #N/A |
=MATCH(50000,{54806;46782;25250;21682},-1) | The LookupArray is in descending order, Type is -1 so the function returns array index of the number just greater than the value 50000. | 1 |
Related LibreOffice functions:
ODF standard:
Equivalent Excel functions:
MATCH