matching values in specified range excel with match function

Match function returns the relative position of an item in a range that matches a specified value in a specified order.

Syntax :


1. Lookup_value is the value you use to find the value you want in a table. (Lookup_value can be a value (number, text, or logical value) or a cell reference to a number, text, or logical value.
2. Lookup_array is a contiguous range of cells containing possible lookup values. Lookup_array must be an array or an array reference.
3.Match_type is the number -1, 0, or 1. Match_type specifies how Microsoft Excel matches lookup_value with values in lookup_array.

  • If match_type is 1, MATCH finds the largest value that is less than or equal to lookup_value. Lookup_array must be placed in ascending order: …-2, -1, 0, 1, 2, …, A-Z, FALSE, TRUE.
  • If match_type is 0, MATCH finds the first value that is exactly equal to lookup_value. Lookup_array can be in any order.
  • If match_type is -1, MATCH finds the smallest value that is greater than or equal to lookup_value. Lookup_array must be placed in descending order: TRUE, FALSE, Z-A, …2, 1, 0, -1, -2, …, and so on.
  • If match_type is omitted, it is assumed to be 1.


In the following example the number which is equal to 76 or less than 76 is to be find out, in the range A2:A8