MATCH vs XMATCH: what's different and which to use

The syntax side by side

MATCH(lookup_value, lookup_array, [match_type]) has three arguments. match_type is 1 (the default: the largest value less than or equal to the lookup value, data sorted ascending), 0 (exact) or -1 (the smallest value greater than or equal to it, data sorted descending).

XMATCH(lookup_value, lookup_array, [match_mode], [search_mode]) has four. match_mode is 0 (the default: exact), -1 (exact or next smaller), 1 (exact or next larger) or 2 (wildcards). search_mode is 1 (first to last, the default), -1 (last to first), 2 (binary search on data sorted ascending) or -2 (binary search on data sorted descending).

With an exact match, both give the same answer. Desk lamp is the first item in this stock count, so both return 1. Neither is case-sensitive: "desk LAMP" finds the same row.

The default is the trap: MATCH without 0

Leave off MATCH's third argument and you get an approximate match that assumes the list is sorted A to Z. On an unsorted list of names it doesn't return #N/A when it should; it can return a wrong position with no warning, and INDEX then pulls a value from the wrong row. That's the most common MATCH bug, and it's why most MATCH formulas you see end in ,0.

XMATCH flips the default. =XMATCH("Desk lamp",A2:A9) is already exact, and a value that isn't in the list gives #N/A instead of a neighbour. If you only take one thing from this page: in MATCH always type the 0 when you want an exact match.

Next smaller vs next larger: the rate table

The table in E1:F6 is a courier price list read as "up to": parcels up to 1 kg cost 4.50, up to 2 kg cost 6, up to 5 kg cost 8.50, and so on. A 3.2 kg parcel belongs in the up-to-5 kg band.

MATCH's default finds the next smaller value, 2, so it returns the up-to-2 kg band and undercharges. To get the next larger value, MATCH needs match_type -1 and the list sorted largest first, which means re-sorting your table. XMATCH with match_mode 1 finds the next larger value whatever the order.

Find the last match: search from the bottom

Desk lamp was counted twice, and the recount further down the list is the one that matters. MATCH always returns the first match. XMATCH with search_mode -1 starts at the bottom and returns the position of the last one, still counted from the top of the list, so INDEX can use it as it is.

In MATCH you'd need a workaround such as LOOKUP(2,1/(A2:A9="Desk lamp"),C2:C9), which is harder to read and to explain to whoever opens the file next.

Wildcards work differently

MATCH with 0 treats * and ? as wildcards automatically, so =MATCH("Monitor*",A2:A9,0) returns 2. XMATCH treats them as ordinary characters unless you ask: =XMATCH("Monitor*",A2:A9) looks for the literal text Monitor* and returns #N/A. Set match_mode to 2 to switch wildcards on.

The flip side: if your data really contains an asterisk or question mark, such as a part called AB*12, MATCH with 0 reads it as a wildcard unless you write AB~*12. XMATCH's exact mode finds it as typed.

Binary search, versions and which to pick

XMATCH's search_mode 2 (ascending) and -2 (descending) use binary search, which halves the list at each step and is much faster on very long sorted lists. On unsorted data it returns wrong positions without an error, just like MATCH's approximate modes, so only use it on data you sort and keep sorted.

XMATCH exists in Excel for Microsoft 365, Excel 2021 and later, and Excel for the web. Open the file in Excel 2019 or older and the formula shows #NAME?, with _xlfn.XMATCH in the formula bar. MATCH works in every version.

Pick XMATCH when everyone has Excel 365 or 2021: the exact default prevents the classic bug, and next-larger, last-match and wildcard lookups need no workarounds. Pick MATCH with 0 when the workbook goes to people on older versions. INDEX with either one is still the way to look up to the left or return a row and column position; XLOOKUP is simpler when you just want the value.