XMATCH returns the position of a value in a row, column or array, and it goes further than MATCH when you need reverse search, binary search or tighter control over match behaviour. Use it when you need a match's position rather than its value, often alongside INDEX; XLOOKUP is usually simpler when you only need the value.
The Complete Excel Course with AI Integration
From formulas and pivot tables to Power Query, DAX, VBA and AI with Copilot, ChatGPT and Claude — hands-on from the first lesson.
By Sagnik Bhattacharya and Virginia Thorn, Primetime Emmy Award winner
Buy nowIt becomes more valuable once you stop treating lookup work as one-size-fits-all.
What Does XMATCH Do in Excel?
XMATCH returns the position of a match, not the matching value itself, and it suits reverse searches and lookups that need more control over the 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 Use XMATCH?
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 Does XMATCH Fit Best?
XMATCH is useful in dynamic models, advanced INDEX combinations, reverse searches, and any setup where the position itself drives a later formula.
If you want to build these lookups with an instructor, try The Complete Excel Course with AI Integration on Coding Liquids.
How Is XMATCH Different From MATCH?
XMATCH differs from MATCH in control: search mode and match handling make it 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.
The Complete Excel Course with AI Integration
From formulas and pivot tables to Power Query, DAX, VBA and AI with Copilot, ChatGPT and Claude — hands-on from the first lesson.
By Sagnik Bhattacharya and Virginia Thorn, Primetime Emmy Award winner
Buy nowCommon 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 Should You Use XLOOKUP Instead of XMATCH?
XLOOKUP may still be simpler when you need the returned value directly. If you want to compare older and newer lookup approaches, VLOOKUP vs XLOOKUP remains useful background.
The Complete Excel Course with AI Integration
From formulas and pivot tables to Power Query, DAX, VBA and AI with Copilot, ChatGPT and Claude — hands-on from the first lesson.
By Sagnik Bhattacharya and Virginia Thorn, Primetime Emmy Award winner
Buy nowFrequently 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?
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, because it returns the result in one step without needing a second function.
How do I do a reverse (last-match) search?
Set XMATCH's search mode to -1, which searches last to first. It returns the position of the most recent matching record, which is exactly what 'latest entry for this customer' patterns need, and you can pass that position to INDEX to return the value from that row.
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 (ascending for 2, descending for -2) or you get wrong results silently. Only use it on genuinely sorted columns.
Why pair XMATCH with INDEX?
Pair them because XMATCH finds the position and INDEX returns the value from any row or column, as in INDEX(range, XMATCH(...)). The result is a flexible lookup that is the modern, readable replacement for INDEX/MATCH, and it keeps the position logic separate from the returned column.
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.
- The Complete Excel Guide 2026: Formulas, AI Copilot, and Modern M365
- Create Lookups With Copilot in Excel: When It Writes XLOOKUP Well and When It Doesn’t
- LET and LAMBDA in Excel: Turn Repeated Formulas Into Reusable Logic
- How to Review AI-Generated Excel Formulas Before You Trust Them
- VLOOKUP vs XLOOKUP: Which Should You Use in Excel?
