How to Use XMATCH in Excel
The modern replacement for MATCH with exact-match default, reverse search and wildcard support built in.
What is XMATCH in Microsoft Excel?
XMATCH returns the position of a value, like MATCH, but defaults to exact matching and adds a search_mode that can scan from the bottom up. That makes finding the last transaction for a customer a one-cell formula instead of an array trick. It also pairs with XLOOKUP for nested lookups.
Formula Syntax
Arguments Breakdown
| Argument | Requirement | Purpose & Details |
|---|---|---|
| lookup_value | Required | The value whose position you want. |
| lookup_array | Required | The one-dimensional range to search. |
| match_mode | Optional | 0 exact (default), -1 exact or next smaller, 1 exact or next larger, 2 wildcard match. |
| search_mode | Optional | 1 first-to-last (default), -1 last-to-first, 2 binary ascending, -2 binary descending. |
When to Use XMATCH (Real-World Business Scenarios)
- 1
Finding the position of the most recent entry with search_mode -1, which searches last-to-first.
- 2
Building two-way lookups where XMATCH supplies both the row and the column index.
- 3
Replacing MATCH formulas that broke because someone forgot the trailing 0 for exact match.
Step-by-Step Practical Business Example
Suppose you have the following dataset in Excel cells A1:D4:
| Order | Customer | Amount |
|---|---|---|
| ORD-101 | Rahul | 1200 |
| ORD-102 | Pooja | 850 |
| ORD-103 | Rahul | 3400 |
How it works: Searches the customer column from the bottom up and returns 3, the position of Rahul's most recent order, instead of 1 which a normal top-down search would give.
Common Errors & How to Fix Them
Why it happens: The value does not exist in the lookup array.
Fix: Confirm spelling and whitespace, or wrap the call in IFNA to return a friendly fallback.
Why it happens: lookup_array is two-dimensional, which XMATCH does not accept.
Fix: Pass a single row or a single column. For a grid, combine two XMATCH calls with INDEX.
Try XMATCH Live in Your Browser
No need to launch desktop Excel. Practice formulas with real-time feedback in our HyperFormula sandbox.
Frequently Asked Questions (XMATCH)
What does the XMATCH formula do in Excel?
XMATCH returns the position of a value, like MATCH, but defaults to exact matching and adds a search_mode that can scan from the bottom up. That makes finding the last transaction for a customer a one-cell formula instead of an array trick. It also pairs with XLOOKUP for nested lookups.
What is the syntax for XMATCH?
What are common mistakes when using XMATCH?
#N/A: The value does not exist in the lookup array. (Confirm spelling and whitespace, or wrap the call in IFNA to return a friendly fallback.) #VALUE!: lookup_array is two-dimensional, which XMATCH does not accept. (Pass a single row or a single column. For a grid, combine two XMATCH calls with INDEX.)
Related Excel Formulas
Keep building your formula toolkit with these lookup & reference functions.
- XLOOKUPThe modern, versatile replacement for VLOOKUP and HLOOKUP that searches in any direction without column index numbers.
- VLOOKUPThe industry-standard vertical lookup function that searches the first column of a table and returns a value in the same row.
- INDEX & MATCHThe robust two-part lookup formula that looks left, looks up, and never breaks when new columns are inserted.
- MATCHReturns the position of a value inside a range — the engine behind INDEX MATCH and most advanced lookups.
- HLOOKUPThe horizontal twin of VLOOKUP: searches across the top row of a table and returns a value from a row below it.
- CHOOSEPicks one value from a list by position — a compact alternative to nested IF for fixed options.
Master XMATCH in Advanced Excel
Learn XMATCH along with our formula library, practice files, and verifiable certificates in our complete curriculum.