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-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 wild cards 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 wild cards 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 wild card.
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 wild cards 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.
searching for text, case is ignored.
Examples |
|---|
Given the following table: |
A | B | C | D | 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. |