MATCH

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.
  • 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.
  • search_type  of 0 indicates exact match, and is required in situations where  range  is not sorted.
  • 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.