Lookup & Reference

XMATCH Function in Excel

Returns the relative position of an item in an array or range of cells. Supports exact match, approximate match, and wildcard matching.

Syntax

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

Arguments

  • lookup_value (required): The value to search for
  • lookup_array (required): The array or range to search
  • match_mode (optional): 0=exact, -1=exact or smaller, 1=exact or larger, 2=wildcard
  • search_mode (optional): 1=first to last, -1=last to first, 2=binary ascending, -2=binary descending

Examples

  • =XMATCH("Apple", A2:A10) - Find position of Apple in list - Result: 3
  • =XMATCH(500, B2:B10, 1) - Find position of 500 or next larger - Result: 5
  • =XMATCH("*phone*", A2:A10, 2) - Wildcard search for phone - Result: 7

Excel XMATCH function documentation

  • XMATCH is a Lookup & Reference function in Microsoft 365 and Excel 2021+ that returns the position of a value in a range.
  • Syntax: =XMATCH(lookup_value, lookup_array, [match_mode], [search_mode]).
  • Modern upgrade to [MATCH](/functions/match/) with wildcard, reverse, and binary search options.
  • Use with [INDEX](/functions/index/) the same way as MATCH: =INDEX(range, XMATCH(...)).

XMATCH match_mode and search_mode

  • match_mode 0: exact match (default behavior most users need).
  • match_mode -1: exact match or next smaller item — lookup_array sorted descending.
  • match_mode 1: exact match or next larger item — lookup_array sorted ascending.
  • match_mode 2: wildcard match (* and ?) — useful for partial SKU or name search.
  • search_mode 1: first to last; -1: last to first; ±2: binary search on sorted data (faster on huge ranges).

Step-by-step: replace MATCH with XMATCH

  • Step 1 — Identify existing =MATCH(A2, range, 0) formulas.
  • Step 2 — Replace with =XMATCH(A2, range, 0) for exact match parity.
  • Step 3 — For last match in unsorted duplicates: =XMATCH(A2, range, 0, -1).
  • Step 4 — For prefix search: =XMATCH("ABC*", range, 2).
  • Step 5 — Wrap INDEX: =INDEX(return_range, XMATCH(lookup, key_range, 0)).

XMATCH vs MATCH vs XLOOKUP

  • XMATCH — position only; pairs with INDEX; wildcards and reverse search.
  • MATCH — works in older Excel; fewer search options.
  • XLOOKUP — returns the value directly; skip INDEX+XMATCH when a one-way lookup suffices.
  • Guide: [INDEX MATCH tutorial](/blog/index-match-excel-guide/) · [XLOOKUP function](/functions/xlookup/).

Worked examples to copy

  • Last occurrence: =XMATCH(A2, $A$2:$A$1000, 0, -1) when duplicate IDs exist.
  • Wildcard prefix: =XMATCH("PRO-*", sku_column, 2) for SKUs starting with PRO-.
  • INDEX combo: =INDEX(C:C, XMATCH(E2, A:A, 0)).
  • Binary on sorted IDs: =XMATCH(E2, sorted_ids, 0, 2) for large monotonic lists.

People also ask

  • Does XMATCH work in Excel 2019? — No on desktop; use MATCH in legacy files.
  • XMATCH vs MATCH performance? — Binary search_mode can speed very large sorted lookups.
  • Can XMATCH search right to left? — search_mode -1 returns the last match position.
  • XMATCH #N/A? — No match for the given match_mode; verify wildcards and sort order.

Common errors

  • #N/A when no match found
  • #VALUE! with invalid match_mode

Use cases

  • Finding positions in lists
  • Dynamic range references
  • Data validation

Frequently asked questions

  • What is XMATCH and how is it different from MATCH? XMATCH is the modern replacement for MATCH with additional features: wildcard matching, binary search for sorted data, and the ability to search from last to first. It defaults to exact match (MATCH defaults to approximate).
  • How do I use XMATCH with INDEX? Combine them for powerful lookups: =INDEX(B:B, XMATCH("Apple", A:A)). XMATCH finds the row position, INDEX returns the value from that row in column B.
  • Can XMATCH search from bottom to top? Yes! Use search_mode -1: =XMATCH(value, range, 0, -1). This finds the last occurrence of a value, unlike MATCH which always finds the first.
  • Is XMATCH available in all Excel versions? XMATCH is only available in Excel 365 and Excel 2021. For older versions, use MATCH function instead.
  • How do I use wildcards with XMATCH? Set match_mode to 2 for wildcard matching. Use * for any characters and ? for single character: =XMATCH("*phone*", A:A, 2) finds any cell containing 'phone'.

Editorial review

  • Reviewed by Excel.Directory Editorial Team. Updated May 2026.

When to use XMATCH

  • Finding positions in lists — common Lookup & Reference scenario for XMATCH.
  • Dynamic range references — common Lookup & Reference scenario for XMATCH.
  • Data validation — common Lookup & Reference scenario for XMATCH.

XMATCH in the Lookup & Reference category

  • Browse all Lookup & Reference functions at /categories/lookup-reference/ for related formulas.
  • XMATCH syntax: =XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])
  • lookup_value (required): The value to search for
  • lookup_array (required): The array or range to search
  • match_mode (optional): 0=exact, -1=exact or smaller, 1=exact or larger, 2=wildcard
  • search_mode (optional): 1=first to last, -1=last to first, 2=binary ascending, -2=binary descending
  • Confirm XMATCH arguments match the syntax shown above before filling down.
  • Lock table and range references with $ when copying formulas across rows or sheets.

Formula checklist before you copy down

  • Confirm XMATCH arguments match the syntax shown above before filling down.
  • Lock table and range references with $ when copying formulas across rows or sheets.
  • If results look wrong, check for text stored as numbers and invisible spaces with TRIM.
  • Spot-check three known input rows manually against expected output.

Related Excel functions

  • MATCH (/functions/match/): Returns the relative position of an item in an array that matches a specified value.
  • XLOOKUP (/functions/xlookup/): Searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, XLOOKUP can return the closest (approximate) match.
  • INDEX (/functions/index/): Returns the value of an element in a table or array, selected by the row and column number indexes.
  • FILTER (/functions/filter/): Filters a range of data based on criteria you define. Returns an array of values that meet the specified conditions.

Comparisons involving this function

  • INDEX MATCH vs XLOOKUP (/compare/index-match-vs-xlookup/): Use XLOOKUP for readability and speed of setup. Use INDEX MATCH when maintaining older files or when you need a structure already built around separate row and column logic.
  • XLOOKUP vs INDEX MATCH (/compare/xlookup-vs-index-match/): Use XLOOKUP for most modern lookup tasks. Use INDEX MATCH when maintaining legacy workbooks or when row and column matching are already separated in the model.

Errors to watch for

  • #N/A when no match found — review causes on linked error pages in the directory.
  • #VALUE! with invalid match_mode — review causes on linked error pages in the directory.

Copy-paste audit workflow

  • Enter XMATCH on three test rows with known expected output documented on a QA tab.
  • Fill down only after absolute references are locked on lookup tables and rate tables.
  • Compare against manual calculation or a calculator for financial and statistical functions.
  • Search this directory for comparison guides when choosing between similar functions in the same category.

XMATCH worked examples to copy

  • =XMATCH("Apple", A2:A10) — Find position of Apple in list. Expected result: 3.
  • =XMATCH(500, B2:B10, 1) — Find position of 500 or next larger. Expected result: 5.
  • =XMATCH("*phone*", A2:A10, 2) — Wildcard search for phone. Expected result: 7.

XMATCH reference summary for crawlers and offline review

  • XMATCH belongs to the Lookup & Reference category in Excel. Returns the relative position of an item in an array or range of cells. Supports exact match, approximate match, and wildcard matching.
  • Full syntax: =XMATCH(lookup_value, lookup_array, [match_mode], [search_mode]). Open /functions/xmatch/ for parameters, FAQs, and related pages.
  • Common mistakes: #N/A when no match found; #VALUE! with invalid match_mode
  • Pair this function with comparison guides when another Excel formula might fit the same task better.
  • Review fix-excel-formula-errors when unexpected errors appear after upgrading Excel or sharing across locales.