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.
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
B
C
D
E
1
10
vel
40
2
20
elit
3
30
lorex
4
felis
50
5
facit
=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.