Excel XMATCH Function

1. 1

   lookup_value

   Required

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

2. 2

   lookup_array

   Required

   The row, column, or array where Excel should search for lookup_value.

3. 3

   match_mode

   Optional

   Controls exact, approximate, or wildcard matching:

   • 0 — Exact match: Returns a position only when XMATCH finds the lookup value exactly. This is the default.
   • -1 — Exact or next smaller: Returns an exact match, or the position of the next smaller item when no exact match exists.
   • 1 — Exact or next larger: Returns an exact match, or the position of the next larger item when no exact match exists.
   • 2 — Wildcard match: Allows wildcard characters such as asterisks and question marks in the lookup value.
4. 4

   search_mode

   Optional

   Controls the direction or method of the search:

   • 1 — First to last: Searches from the first item to the last item. This is the default.
   • -1 — Last to first: Searches from the last item to the first item.
   • 2 — Binary ascending: Uses binary search on data sorted in ascending order.
   • -2 — Binary descending: Uses binary search on data sorted in descending order.

• It returns a position, not a value

  XMATCH 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.

• Search direction affects duplicates

  The default search_mode returns the first match. Use -1 to search from last to first and return the final match instead.

• Binary search requires sorted data

  Only use search_mode values 2 or -2 when the lookup array is sorted in the matching direction.

Replacing an older position lookup? XMATCH uses exact matching by default and can search in reverse, unlike MATCH.