What it does
XMATCH is a modern version of MATCH with clearer options for exact matching and search direction.
Syntax or pattern
=XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])✅
5 practical examples
1
Find exact position
Return the position of a product in a list.
=XMATCH(A2, Products[Product])XMATCH defaults to exact match in many common cases.
2
Search from bottom to top
Find the last occurrence of a value.
=XMATCH(A2, Products[Product], 0, -1)Search mode -1 scans from the end.
3
Find a column by header
Locate a month header in a table.
=XMATCH(H1, Sales[#Headers])Useful for dynamic column selection.
4
Use XMATCH with INDEX
Return a price using XMATCH as the position finder.
=INDEX(Products[Price], XMATCH(A2, Products[Product]))A modern alternative to INDEX MATCH.
5
Find next larger value
Match a value to the next larger threshold.
=XMATCH(B2, Thresholds, 1)Helpful for bands, levels and tiers.
Common mistakes to avoid
- Mixing up match_mode and search_mode.
- Using approximate modes without understanding thresholds.
- Assuming duplicates are handled automatically.
Related Excel examples
FAQ
Why use XMATCH?
It has clearer defaults and supports reverse searches more directly than MATCH.