Excel MATCH Function

1. 1

   lookup_value

   Required

   The value to find in the lookup array. It can be text, a number, a cell reference, or another formula result.

2. 2

   lookup_array

   Required

   The single row or single column where Excel should search for lookup_value.

3. 3

   match_type

   Optional

   Controls how Excel matches the lookup value:

   • 0 — Exact match: Returns the position only when MATCH finds the lookup value exactly.
   • 1 — Less than: Returns the largest value less than or equal to the lookup value. This is the default and requires ascending sort order.
   • -1 — Greater than: Returns the smallest value greater than or equal to the lookup value and requires descending sort order.

• It returns a position, not a value

  MATCH returns the matched item's relative position, not the item itself. Pair it with INDEX when you need to return data from that position.

• Missing matches return an error

  With exact matching, a lookup value that is not present returns #N/A.

• Exact match is not the default

  If match_type is omitted, Excel uses 1, which is an approximate match mode.

• Approximate modes require sorted data

  Use ascending sort order for 1 and descending sort order for -1. Unsorted data can produce incorrect positions.

• The lookup array must be one-dimensional

  MATCH searches a single row or a single column. It is not designed to scan a full two-dimensional table by itself.

Need exact matching by default or reverse search? Use XMATCH for a modern position lookup with configurable match and search modes.