Lookup · modern position match · Nov 2026

The XMATCH function,
measured & explained.

Syntax, a live playground, a measured position-lookup gallery, match_mode/search_mode, and a sourced error library: the modern XMATCH function.

D1
fx
=XMATCH("Bob",A2:A8)
0
measured gallery rows
0
sourced errors fixed
0
platforms checked
// 01 — Anatomy

How XMATCH works

Returns the relative position of a value in a range, exact match by default. Available in Excel 365 / 2021 and Excel Online.

=XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])
lookup_valueThe value to find.
lookup_arrayThe single row or column to search.
match_mode0 exact (default), -1 exact-or-next-smaller, 1 exact-or-next-larger, 2 wildcard.
search_mode1 first→last (default), -1 last→first, 2/-2 binary search.
.bool-moat td.res-text{color:var(--cyan);font-weight:700;font-family:var(--mono)}
// 02 — Modern position lookup

What XMATCH adds over MATCH

XMATCH returns the position of a value in a range — the modern successor to MATCH. This page's engine evaluates the MATCH equivalents live (XMATCH itself is Excel 365-only); the feature matrix below documents what XMATCH adds. Every live row was measured in HyperFormula. Click any row to load it.

Position-lookup gallery — live MATCH equivalents

ExpressionResultWhy
=MATCH("Bob",A2:A8,0)2Position of Bob (exact)
=MATCH(300,C2:C8,0)4Position of the value 300
=MATCH("Alice",A2:A8,0)1First occurrence of Alice
=IFERROR(MATCH("Zed",A2:A8,0),"not found")not foundGuarded miss
=INDEX(A2:A8,MATCH(300,C2:C8,0))CarolINDEX + MATCH lookup
=MATCH(250,C2:C8,0)5Position of the value 250
=ISNUMBER(MATCH("Dave",A2:A8,0))TRUEExistence test
=MATCH("Dave",A2:A8,0)7Position of Dave

XMATCH vs MATCH — the feature matrix (Excel behavior)

ExpressionResultWhy
=XMATCH("Bob",A2:A8)2default match_mode is exact (0)
=MATCH("Bob",A2:A8,0)2MATCH needs explicit 0 for exact
=XMATCH("B*",A2:A8,0,2)2match_mode 2 = wildcard
=XMATCH(300,C2:C8,0,-1)4search_mode -1 = search from last
=XMATCH(220,C2:C8,1)4match_mode 1 = next larger item
⚠ XMATCH defaults to exact match (match_mode 0); MATCH defaults to approximate (match_type 1) and needs an explicit 0 for exact. Forgetting the 0 in MATCH on unsorted data is the classic wrong-position bug.
XMATCH adds match_mode (0 exact, -1 exact-or-next-smaller, 1 exact-or-next-larger, 2 wildcard) and search_mode (1 first→last, -1 last→first, 2/-2 binary search). It returns a position, so pair it with INDEX for a two-way lookup.
// 03 — Try it live

Interactive XMATCH playground

Edit the table or type any formula — the HyperFormula engine evaluates the MATCH equivalents instantly. Load a preset to find a position, guard a miss, and build an INDEX + MATCH lookup.

A1Sample data — click any cell to edit, the formula re-evaluates live
Result
—
✓ readyHyperFormula 3.5
// 04 — Error library

The real-world error library

8 failure patterns mined from actual user reports on Reddit, Stack Overflow and Super User — each with the authentic symptom, root cause, a fix, and a runnable formula. Not textbook theory.

// 05 — Cross-platform

Where XMATCH works

XMATCH requires Excel 365 / 2021, Excel Online or Google Sheets. Excel 2019 and earlier return #NAME?; use MATCH(...,0) there.

PlatformSupportNotesGotcha
✓ Supported full XMATCH◐ Partial works with caveatsCompiled from public docs · verify against your build
// Go deeper

Master modern lookups

XMATCH is one piece. The full course walks the toolkit end to end with live, measured examples.

  • XMATCH vs MATCH — defaults and modes
  • match_mode: exact, next-smaller, next-larger, wildcard
  • search_mode: forward, reverse, binary
  • INDEX + XMATCH two-way lookups
Free while in beta
Browse the function library

No account needed. Every page has a live playground.

// 07 — FAQ

Frequently asked