Returns the relative position of an item in a range that matches a specified value.
Sample Usage
MATCH("Sunday",A2:A9,0)
MATCH(DATE(2012,1,1),A2:F2)
Syntax
MATCH(search_key, range, [search_type])
-
search_key
- The value to search for. For example,42
,"Cats"
, orI24
. -
range
- The one-dimensional array to be searched.- If a range with both height and width greater than 1 is used,
MATCH
will return#N/A!
.
- If a range with both height and width greater than 1 is used,
-
search_type
- [ OPTIONAL -1
by default ] - The manner in which to search.-
1
, the default, causesMATCH
to assume that the range is sorted in ascending order and return the largest value less than or equal tosearch_key
. -
0
indicates exact match, and is required in situations whererange
is not sorted. -
-1
causesMATCH
to assume that the range is sorted in descending order and return the smallest value greater than or equal tosearch_key
.
-
Notes
MATCH
returns the position in an array or range of a matched value rather than the value itself. To return the value itself or another value corresponding to the row or column the match is found in, useINDEX
,HLOOKUP
, orVLOOKUP
.
See Also
VLOOKUP
: Vertical lookup. Searches down the first column of a range for a key and returns the value of a specified cell in the row found.
HLOOKUP
: Horizontal lookup. Searches across the first row of a range for a key and returns the value of a specified cell in the column found.
INDEX
: Returns the content of a cell, specified by row and column offset.
Examples
Returns the relative position of an item in an array that matches a specified value based on different search_type
.