XMATCH - Find positions with an exact default
Locate a customer for an INDEX calculation and learn how Excel's reverse search chooses a later occurrence.
By the end, you can
- Use exact XMATCH to locate a record.
- Keep retrieved values aligned to the same position.
- Choose forward, reverse, or approximate Excel behavior deliberately.
Before you start: Understand MATCH positions and INDEX retrievals.
In this lesson
A modern position lookup
XMATCH returns a key's relative position in a row or column. Unlike MATCH, its default is exact. An order desk can find a customer's position and use INDEX to retrieve the credit limit and used amount from the same record. XLOOKUP retrieves a value directly; XMATCH supplies a position for other formulas.
Syntax and optional modes
=XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])Both mode arguments are optional. Default matching is exact and default searching is first to last.
| Argument | Purpose | Example |
|---|---|---|
lookup_valueRequired | The key whose relative position is requested. | D2 |
lookup_arrayRequired | The one-dimensional row or column to search. | A2:A5 |
match_modeOptional | 0 exact (default), -1 exact/next smaller, 1 exact/next larger, 2 wildcard. | 0 |
search_modeOptional | 1 forward, -1 reverse, 2 ascending binary, -2 descending binary. | -1 |
Remaining credit from two aligned vectors
₹15,000| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Customer ID | Limit | Used | Requested ID | Available |
| 2 | C101 | ₹25,000 | ₹10,000 | C104 | ₹15,000 |
| 3 | C102 | ₹40,000 | ₹12,000 | C103 | ₹0 |
| 4 | C103 | ₹18,000 | ₹22,000 | C101 | ₹15,000 |
| 5 | C104 | ₹60,000 | ₹45,000 |
Find a later occurrence in Excel
T101| A | B | |
|---|---|---|
| 1 | Ticket | Status |
| 2 | T101 | Open |
| 3 | T102 | Open |
| 4 | T101 | Closed |
| 5 | T103 | Open |
=XMATCH("T101",A2:A5,0,-1)Reverse search finds the later T101 at position 3.
=INDEX(B2:B5,XMATCH("T101",A2:A5,0,-1))INDEX reads Closed from the position returned by reverse XMATCH.
Modes, missing keys, and compatibility
- No exact match gives #N/A unless a surrounding formula handles that result. A position is always relative to the lookup vector.
- Text matching is generally case-insensitive. Wildcard interpretation needs match_mode 2; in default exact mode, wildcard symbols are not requested as patterns.
- Mode -1 chooses the next smaller value and mode 1 the next larger when exact matching fails. Binary search modes require sorted ascending/descending input; do not confuse them with ordinary forward/reverse search.
- XMATCH is available in Microsoft 365 and Excel 2021/2024. Older versions can often use MATCH(...,0) for the exact forward case.
Check your understanding
Choose an answer for instant feedback. You can try again; answers stay only on this page.