Lookup & Reference
XMATCH function: exact position by default, next-larger tiers, and reverse search
Find 1-based item positions with XMATCH—defaulting safely to exact match, finding the last occurrence with search_mode -1, or matching unsorted bracket thresholds.
Three upgrades XMATCH brings over classic MATCH
Classic MATCH defaults to approximate mode (1) when its third argument is omitted—a frequent cause of silent wrong answers on unsorted lists. XMATCH(lookup_value, lookup_array, [match_mode], [search_mode]) defaults to exact match (0) automatically.
Even better, match_mode = 1 ("exact match or next larger item") and -1 ("exact match or next smaller item") do not require pre-sorting the lookup array, and search_mode = -1 scans from last to first to find the latest log entry.
| Formula | Mode | Result on [SKU-A, SKU-B, SKU-A, SKU-C] |
|---|---|---|
| =XMATCH("SKU-A", A2:A5) | Default exact, first-to-last | 1 (first SKU-A) |
| =XMATCH("SKU-A", A2:A5, 0, -1) | Exact, reverse last-to-first | 3 (latest SKU-A) |
| =XMATCH(350, B2:B5, 1) | Exact or next larger in [100,250,500,1000] | 3 (500 bracket) |
| =XMATCH("SKU-Z", A2:A5) | Missing key | #N/A |
Find the latest occurrence with INDEX and XMATCH
Pairing INDEX(C2:C5, XMATCH(D2, A2:A5, 0, -1)) retrieves the status or price from the bottom-most row matching D2 in a chronological activity log.
=INDEX(C2:C5, XMATCH(D2, A2:A5, 0, -1))Next-larger tier and missing values
With match_mode = 1, searching 350 against [100, 250, 500, 1000] returns position 3 (500), whereas searching 1200 (above the largest bracket) returns #N/A.
=XMATCH(E2, B2:B5, 1)Try the example
Use the original values or change them to test your understanding.