Documentation/Calc Functions/MATCH

From The Document Foundation Wiki
Jump to navigation Jump to search


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.
Gnome-dialog-warning.svg

Warning:
When using functions where one or more arguments are search criteria strings that represents a regular expression, the first attempt is to convert the string criteria to numbers. For example, ".0" will convert to 0.0 and so on. If successful, the match will not be a regular expression match but a numeric match. However, when switching to a locale where the decimal separator is not the dot makes the regular expression conversion work. To force the evaluation of the regular expression instead of a numeric expression, use some expression that can not be misread as numeric, such as ".[0]" or ".\0" or "(?i).0".


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:

HLOOKUP

LOOKUP

OFFSET

VLOOKUP

ODF standard:

Section 6.14.9, part 2

Equivalent Excel functions:

MATCH