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. 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