XMATCH

XMATCH is the modern upgrade to the classic MATCH function. Where MATCH finds the position of a value in a range, XMATCH does the same thing — but with more control over how it searches, which direction it searches in, and how it handles approximate matches. On its own it is useful for ranking and position work. Paired with INDEX or XLOOKUP, it becomes a precision tool for complex data retrieval. If you are on Microsoft 365, XMATCH should replace MATCH in all new formulas you write.

MATCH Recap — What It Does

Before looking at XMATCH, a quick recap of what MATCH does. It returns the position — the row or column number — of a value within a range. Not the value itself, just its location. That position number is then typically fed into INDEX or used in other calculations.

=MATCH(lookup_value, lookup_array, [match_type])

Products in A2:A6: Widget A, Widget B, Widget C, Widget D, Widget E

=MATCH("Widget C", A2:A6, 0)   →  3   (Widget C is in position 3)
=MATCH("Widget E", A2:A6, 0)   →  5   (Widget E is in position 5)
=MATCH("Widget X", A2:A6, 0)   →  #N/A (not found)

MATCH is the backbone of INDEX/MATCH lookups. It is also useful independently — for finding where something ranks in a list, checking whether a value exists, or calculating dynamic offsets. XMATCH does all of this with fewer limitations.

XMATCH Syntax

XMATCH has four arguments — the first two are identical to MATCH. The third and fourth give you control over the type of matching and the direction of the search.

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

  lookup_value   →  The value to find
  lookup_array   →  The range to search in (one row or one column)
  match_mode     →  0 = exact (default), -1 = exact or next smaller,
                    1 = exact or next larger, 2 = wildcard
  search_mode    →  1 = first to last (default), -1 = last to first,
                    2 = binary search ascending, -2 = binary descending

Basic XMATCH — Finding a Position

The most common use of XMATCH is finding the row or column number of a value — either to use in an INDEX formula, or to answer a positional question like "which rank does this product hold?"

Find Sarah's position in the leaderboard:
=XMATCH("Sarah", A2:A6)          →  3

Check if a value exists (returns position or #N/A):
=XMATCH("Sarah", A2:A6)          →  3   (exists — position 3)
=XMATCH("Ahmed", A2:A6)          →  #N/A (not in list)

Use ISNUMBER to turn this into a clean TRUE/FALSE check:
=ISNUMBER(XMATCH("Sarah", A2:A6))  →  TRUE
=ISNUMBER(XMATCH("Ahmed", A2:A6))  →  FALSE

The ISNUMBER(XMATCH()) pattern is one of the most useful combinations in Excel 365 — it cleanly answers "does this value exist in this list?" without returning a position number or an error. You will use this constantly in data validation, dashboard logic, and conditional formatting.

XMATCH With INDEX — Cleaner Lookups

XMATCH is most powerful when paired with INDEX. The combination works identically to INDEX/MATCH but with the added benefits of XMATCH's search modes and match modes. The pattern is simple: XMATCH finds the row number, INDEX returns the value at that row.

Employee table: A = ID, B = Name, C = Department, D = Salary

Classic INDEX/MATCH:
=INDEX(D2:D6, MATCH("E003", A2:A6, 0))     →  £48,000

Modern INDEX/XMATCH (identical result, cleaner):
=INDEX(D2:D6, XMATCH("E003", A2:A6))       →  £48,000

Two-way lookup with INDEX/XMATCH/XMATCH:
=INDEX(B2:D6, XMATCH("E003", A2:A6), XMATCH("Salary", B1:D1))
→  £48,000  (finds row by ID, column by header name)

The two-way INDEX/XMATCH/XMATCH pattern is the cleanest way to do a matrix lookup in Excel 365. Both XMATCH calls are exact by default — no need to type a 0 match type argument — which makes the formula shorter and easier to read than the INDEX/MATCH/MATCH equivalent.

Search Mode — Finding the Last Match

Just like XLOOKUP, XMATCH lets you control search direction. Setting search_mode to -1 finds the last occurrence of a value — the most recent entry in a list sorted oldest to newest. This is the XMATCH equivalent of XLOOKUP's reverse search, and it is particularly useful in audit logs and transaction histories.

Transaction log — Customer IDs in A2:A20, amounts in B2:B20

Find position of FIRST entry for C005:
=XMATCH("C005", A2:A20)            →  1   (search_mode 1 = default)

Find position of LAST entry for C005:
=XMATCH("C005", A2:A20, 0, -1)    →  4   (search_mode -1 = last to first)

Use with INDEX to return the most recent amount:
=INDEX(B2:B20, XMATCH("C005", A2:A20, 0, -1))   →  £2,250

The search_mode argument is the fourth argument in XMATCH. When using it, you must also specify the match_mode (third argument) — use 0 for exact match — even if you were otherwise happy with the default. Both optional arguments must be provided in order.

Match Mode — Approximate and Wildcard

XMATCH supports the same match modes as XLOOKUP. Approximate match (-1 or 1) is useful for tier-based lookups. Wildcard match (2) lets you search using * and ? patterns — finding partial text matches inside a list.

Approximate match — find tier position for a score:
Score bands in A2:A5: 0, 50, 75, 90
Score in F2: 82

=XMATCH(F2, A2:A5, -1)   →  3   (82 matches the 75 band — position 3)

Then use with INDEX to return the grade:
Grades in B2:B5: Fail, Pass, Merit, Distinction
=INDEX(B2:B5, XMATCH(F2, A2:A5, -1))   →  "Merit"

Wildcard match — find position of first product containing "Pro":
Products in A2:A10: Basic, Standard, Pro Max, Pro Lite, Premium…
=XMATCH("Pro*", A2:A10, 2)   →  3   (Pro Max is at position 3)

Checking List Membership — XMATCH as an Existence Test

One of the most practical standalone uses of XMATCH is checking whether a value exists in a list at all. This is cleaner than COUNTIF for this purpose because XMATCH tells you the position if it exists, or returns an error if it does not — and wrapping it in ISNUMBER converts that into a clean TRUE/FALSE.

Approved suppliers list in A2:A20.
Check if the supplier in F2 is on the approved list:

=ISNUMBER(XMATCH(F2, A2:A20))
  →  TRUE if found,  FALSE if not

Use in a conditional:
=IF(ISNUMBER(XMATCH(F2, A2:A20)), "Approved", "Not on list")

Use in conditional formatting to highlight unapproved entries:
Formula: =NOT(ISNUMBER(XMATCH(A2, ApprovedList)))
Applies red fill to any cell in column A not found in the approved list.

This pattern — ISNUMBER(XMATCH()) inside IF or conditional formatting — is cleaner than the COUNTIF equivalent because XMATCH is designed for finding positions, not counting, so it stops at the first match and is faster on large lists.

Relative Position for Dynamic Ranges

Because XMATCH returns a position number, it is also useful for building dynamic formula ranges — where the start or end of a range depends on a value in the data rather than a fixed row number. A common example is summing from a start date to a specific month in a row of monthly figures.

Monthly sales headers in B1:M1: Jan, Feb, Mar, Apr, May … Dec
Monthly sales figures in B2:M2

Sum from January to the month named in cell P1:
=SUM(INDEX(B2:M2, 1, 1) : INDEX(B2:M2, 1, XMATCH(P1, B1:M1)))

If P1 = "Apr":
  XMATCH("Apr", B1:M1) → 4
  INDEX(B2:M2, 1, 4)   → the cell containing April's figure
  SUM(Jan cell : Apr cell) → YTD total through April

MATCH vs XMATCH — Quick Reference

One important gotcha in the table above: the classic MATCH function defaults to approximate match (type 1) when the third argument is omitted. Many beginners accidentally leave it out and get wrong results. XMATCH defaults to exact match when match_mode is omitted — a much safer default that eliminates a common source of silent errors.

Next up — Dynamic Arrays, where you will learn how Excel 365's spill engine works and how functions like FILTER, SORT, and UNIQUE can return entire tables of results from a single formula.