XMATCH is easy to overlook because it sounds like a supporting actor next to XLOOKUP. But it solves a useful part of the lookup problem on its own, especially when you need position, reverse search, or tighter control over match behaviour.
Complete Excel Guide with AI Integration
Master formulas, pivot tables, data analysis, and charts — with AI integration.
Learn moreIt becomes more valuable once you stop treating lookup work as one-size-fits-all.
Quick answer
Use XMATCH when you need the position of a match rather than the returned value, when you want reverse searches, or when you need more control over the lookup mechanics than MATCH gives you.
- You need the position of a result, not just the value.
- You want to search from the end of a list.
- You are building more precise modern lookup logic.
Why XMATCH matters
XMATCH gives you a cleaner way to control lookup position and search direction. That opens up patterns that feel awkward with older lookup tools.
Where it fits best
It is useful in dynamic models, advanced INDEX combinations, reverse searches, and any setup where the position itself drives a later formula.
Why it is not just MATCH with a new name
The practical difference is control. Search mode and match handling make XMATCH far more useful once you move beyond the simplest left-to-right lookup questions.
Worked example: latest matching record
An operations sheet needs the latest matching record for a customer rather than the first one. XMATCH can search from the bottom of the list and return the position the later formula needs.
Common mistakes
- Using it when XLOOKUP already solves the problem more directly.
- Ignoring sort assumptions for any binary-search style pattern.
- Forgetting that position and value are different outputs.
When to use something else
If you need the returned value directly, XLOOKUP may still be simpler. If you want to compare older and newer lookup approaches, VLOOKUP vs XLOOKUP remains useful background.
Frequently asked questions
How is XMATCH different from MATCH?
XMATCH adds a search-mode argument (including last-to-first and binary search) and defaults to exact match, while still returning a position like MATCH. That extra control is the point: reverse searches and approximate modes that are awkward with MATCH become straightforward.
When should I use XMATCH instead of XLOOKUP?
When you need the position of a match to feed INDEX or a later formula, rather than the value itself. If you just want the returned value, XLOOKUP is usually simpler.
How do I do a reverse (last-match) search?
Set XMATCH's search mode to -1 (last to first). It returns the position of the most recent matching record, which is exactly what 'latest entry for this customer' patterns need.
What does binary search mode do, and when is it safe?
Search modes 2 and -2 use binary search, which is fast on large lists, but the data must be sorted in the matching direction or you get wrong results silently. Only use it on genuinely sorted columns.
Why pair XMATCH with INDEX?
INDEX(range, XMATCH(...)) gives a flexible lookup: XMATCH finds the position and INDEX returns the value from any row or column. It is the modern, readable replacement for INDEX/MATCH.
XMATCH returns #N/A — what is wrong?
Usually no exact match exists in exact mode, a type mismatch (a number stored as text), or stray spaces. Check the lookup value's type and trim spaces, and switch to wildcard or approximate mode only if that is genuinely intended.
Related guides on this site
If you want to keep going without opening dead ends, these are the most useful next reads from this site.