MATCH

The MATCH function returns the position of a value within a collection.

Tip: For an enhanced search that works in any direction, use XMATCH.

MATCH(search-for, search-where, matching-method)

search-for: The value to find. search-for can contain any value, or a REGEX string.

search-where: The collection containing the values to be searched. search-where can contain any values.

matching-method: An optional modal value specifying how value matching is performed.

find largest value (1 or omitted): Find the cell with the largest value less than or equal to search-for. If you use find largest value, you can’t use wildcards in search-for.

find value (0): Find the first cell with a value that exactly matches search-for. This matching method is best for locating text. If you use find value, you can use wildcards in search-for. You can use a ? (question mark) to represent one character, an * (asterisk) to represent multiple characters, and a ~ (tilde) to specify that the following character should be matched rather than used as a wildcard.

find smallest value (–1): Find the cell with the smallest value greater than or equal to search-for. If you use find smallest value, you can’t use wildcards in search-for.

Notes

MATCH works only on a collection that is part of a single row or column; you can’t use it to search a two-dimensional collection.
Cell numbering starts with 1 at the top or left cell for vertical and horizontal collections, respectively. Searches are performed top-to-bottom or left-to-right.
When searching for text, case is ignored.

Examples
Given the following table:

	A	C	E
1	10	vel	40
2	20	elit	20
3	30	lorex	30
4	40	felis	50
5	50	facit	10

=MATCH(40, A1:A5) returns 4, because 40 (search-for) is the fourth item in the specified collection (search-where).

=MATCH(40, E1:E5) returns 1, because 40 is the first item in the specified collection.

=MATCH(35, E1:E5, 1) returns 3, because 30 is the largest value less than or equal to 35 (matching-method is 1, find largest value).

=MATCH(35, E1:E5, -1) returns 1, because 40 is the smallest value greater than or equal to 35 (matching-method is -1, find smallest value).

=MATCH(35, E1:E5, 0) returns an error, because no exact match can be found (matching-method is 0, find value).

=MATCH("vel", C1:C5) returns 1, because "vel" appears in the first cell of the specified range.

=MATCH("*x", C1:C5, 0) returns 3, because "lorex", which ends with an "x", appears in the third cell of the range.

=MATCH("vel", C1:D5) returns an error, because the search-for collection can only include one row or one column.

=MATCH(REGEX("^f.*"), C1:C5,0) returns 4, because the first word that starts with an "f" is the fourth item in the collection.

See alsoLOOKUP

Helpful?

Formulas and Functions Help

MATCH

Notes