Returns the position of an item in a range that matches a specified value.
Sample Usage
MATCH(40,A1:A10,1)
MATCH(40,A1:A10,-1)
MATCH("Sunday",A2:A9,0)
Syntax
MATCH(value, range, [search_type])
- value - The value to search for.
- range - The range of cells being searched.
- search_type - [OPTIONAL] Specifies how Spreadsheet.com matches ‘value’ with elements in ‘range’.
Examples
MATCH(40,A1:A10,1) searches for the number 40 in the range A1:A10. If an exact match is found, the position the matched value is returned. If there is no exact match, the position of the next lower value in the range is returned.
MATCH(40,C1:C10,-1) searches for the number 40 in the range A1:A10. If an exact match is found, the position the matched value is returned. If there is no exact match, the position of the next higher value in the range is returned.
MATCH("Sunday",A2:A9,0) searches for an exact match for the string "Sunday" in the range A2:A9.
Notes
- MATCH returns the position in an array or range of a matched value rather than the value itself.
- If range with both height and width greater than 1 is used, MATCH will return #N/A.
- A search_type of 1 [default] causes MATCH to return the largest value less than or equal to value . Use this search_type only when range is sorted in ascending order.
- A search_type of 0 indicates exact match, and is required in situations where range is not sorted.
- A search_type of -1 causes MATCH to return the smallest value greater than or equal to value . Use this search_type only when range is sorted in descending order.