Documentation/Calc Functions/MATCH

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

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 (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; if not, 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.

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.

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.

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.

Related LibreOffice functions:
HLOOKUP

LOOKUP

OFFSET

VLOOKUP

ODF standard:
Section 6.14.9, part 2

Equivalent Excel functions:
MATCH