Skip to content
Google Sheets

XMATCH function in Google Sheets

Returns the relative position of an item in an array or range that matches a specified value.

=XMATCH(search_key, lookup_range, [match_mode], [search_mode])

XMATCH syntax and parameters

Four arguments, two required.

  • search_keyanyRequired

    The value to search for. For example, 42, "Cats", or B24.

  • lookup_rangerangeRequired

    The range to consider for the search. This range must be a singular row or column.

  • match_modenumberOptional

    [OPTIONAL: 0 by default] The manner in which to find a match for the search_key. 0 is for an exact match. 1 is for an exact match or the next value that's greater than the search_key. -1 is for an exact match or the next value that's lesser than the search_key. 2 is for a wildcard match.

  • search_modenumberOptional

    [OPTIONAL: 1 by default] The manner in which to search through the lookup range. 1 is to search from the first entry to the last. -1 is to search from the last entry to the first. 2 is to search through the range with binary search, sorted in ascending order. -2 is to search through the range with binary search, sorted in descending order.

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 Excel

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 Google Sheets gets this done.