Syntax, a live playground, a measured position-lookup gallery, match_mode/search_mode, and a sourced error library: the modern XMATCH function.
Returns the relative position of a value in a range, exact match by default. Available in Excel 365 / 2021 and Excel Online.
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.
| Expression | Result | Why |
|---|---|---|
| =MATCH("Bob",A2:A8,0) | 2 | Position of Bob (exact) |
| =MATCH(300,C2:C8,0) | 4 | Position of the value 300 |
| =MATCH("Alice",A2:A8,0) | 1 | First occurrence of Alice |
| =IFERROR(MATCH("Zed",A2:A8,0),"not found") | not found | Guarded miss |
| =INDEX(A2:A8,MATCH(300,C2:C8,0)) | Carol | INDEX + MATCH lookup |
| =MATCH(250,C2:C8,0) | 5 | Position of the value 250 |
| =ISNUMBER(MATCH("Dave",A2:A8,0)) | TRUE | Existence test |
| =MATCH("Dave",A2:A8,0) | 7 | Position of Dave |
| Expression | Result | Why |
|---|---|---|
| =XMATCH("Bob",A2:A8) | 2 | default match_mode is exact (0) |
| =MATCH("Bob",A2:A8,0) | 2 | MATCH needs explicit 0 for exact |
| =XMATCH("B*",A2:A8,0,2) | 2 | match_mode 2 = wildcard |
| =XMATCH(300,C2:C8,0,-1) | 4 | search_mode -1 = search from last |
| =XMATCH(220,C2:C8,1) | 4 | match_mode 1 = next larger item |
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.
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.
XMATCH requires Excel 365 / 2021, Excel Online or Google Sheets. Excel 2019 and earlier return #NAME?; use MATCH(...,0) there.
| Platform | Support | Notes | Gotcha |
|---|
XMATCH is one piece. The full course walks the toolkit end to end with live, measured examples.