MATCH function

MATCH returns the relative position of an item in an array that matches a specified value. The function returns the position of the value found in the lookup_array as a number.


MATCH(search criterion,lookup_array,type)

  • search criterion is the value which is to be searched for in the single-row or single-column array.
  • lookup_array is the reference that is searched. A lookup array can be a single row or column, or part of a single row or column.
  • type can take the values 1, 0, or -1.
If type equals 1 or if this optional argument is missing, it is assumed that the first column of the search array is sorted in ascending order. The last value that is smaller or equal to the search criterion is returned. This result applies even when the search array is not sorted.
If type equals 0, only exact matches are found. If the search criterion is found more than once, the function returns the first one found. Only when type equals 0 can you search for regular expressions.
If type equals -1 it is assumed that the column in sorted in descending order. The first value that is larger or equal to the search criterion is returned.

The results from the values 1 and -1 correspond to those of the same functions in Microsoft Excel.


=MATCH(200,D1:D100) searches the area D1:D100, which is sorted by column D, for the value 200. As soon as this value is reached, the number of the row in which it was found is returned. If a higher value is found during the search in the column, the number of the previous row is returned.