Skip to content
ExcelExcel 2021+

XMATCH function in Excel

Returns the relative position of an item in an array or range of cells.

=XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])

XMATCH syntax and parameters

Four arguments, two required.

  • lookup_valueanyRequired

    The lookup value

  • lookup_arrayarrayRequired

    The array or range to search

  • match_modenumberOptional

    Specify the match type:0 - Exact match (default)-1 - Exact match or next smallest item1 - Exact match or next largest item2 - A wildcard match where *, ?, and ~ have special meaning.

  • search_modenumberOptional

    Specify the search type:1 - Search first-to-last (default)-1 - Search last-to-first (reverse search).2 - Perform a binary search that relies on lookup_array being sorted in ascending order. If not sorted, invalid results will be returned.-2 - Perform a binary search that relies on lookup_array being sorted in descending order. If not sorted, invalid results will be returned.

XMATCH examples

Formulas you'll actually reuse.

  1. Which row holds the Q3 actuals? Exact match by default:

    =XMATCH("Q3 actuals", A2:A40)

    Result23

  2. Search bottom-up to find a customer's most recent order row:

    =XMATCH(F2, B2:B200, 0, -1)

    Result187

XMATCH in Google Sheets

Same name — your formula ports as-is.

Try XMATCH in the playground

Edit the example — nothing to install.

Preloaded with the XMATCH formula from Example 1 — change anything and watch it respond.

Loading the editor…

XMATCH errors

What they mean — and the fixes.

  • #N/A

    No exact match found — check for stray spaces, or pass match_mode 1/-1 to accept the next larger/smaller value.

Related functions

More ways Excel gets this done.