What it does
XMATCH does not return the value itself but its position: if the value is in the third cell of the range, the result is 3. On its own that answers "is it in the list, and where?"; combined with INDEX it lets you pull a value from another column.
It is the modern version of MATCH and differs in three ways: exact match is the default, approximate match does not need a sorted list, and the fourth argument can start the search from the end. Use match_mode 2 for wildcards (*, ?).
If you want the value rather than its position, XLOOKUP is shorter. For files that will be opened in Excel 2019 or earlier, use MATCH.
Syntax
=XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])=ÇAPRAZEŞLEŞTİR(aranan; arama_dizisi; [eşleştirme_modu]; [arama_modu])Arguments in square brackets are optional. English Excel separates arguments with commas (,) and uses a dot for decimals; Turkish Excel uses semicolons (;) and a decimal comma. Excel shows function names in its own language when it opens the file.
Arguments
| Argument | Required | Description |
|---|---|---|
lookup_value | Required | The value whose position you want, or the cell that holds it. The search is not case-sensitive. |
lookup_array | Required | The single column or row to search, e.g. B2:B7. |
match_mode | Optional | 0 or omitted: exact match. -1: exact match or the next smaller value. 1: exact match or the next larger value. 2: wildcard match. |
search_mode | Optional | 1 or omitted: search from first to last. -1: search from last to first and return the position of the last match. 2 and -2: binary search on a sorted list. |
Examples
1Find a customer's latest order
Orders are listed by date. In F2 the search starts from the end (search_mode -1) and returns the position of the last row for the customer in E2; in G2, INDEX takes the date at that position.
=XMATCH(E2,B2:B7,0,-1)| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Date | Customer | Amount | Customer | Last Order Position | Last Order Date | |
| 2 | 2-Sep-2026 | North Market | 3,200 | North Market | 5 | 14-Oct-2026 | |
| 3 | 9-Sep-2026 | Atlas Ltd | 1,850 | ||||
| 4 | 17-Sep-2026 | North Market | 4,100 | ||||
| 5 | 3-Oct-2026 | Blue Electric | 2,600 | ||||
| 6 | 14-Oct-2026 | North Market | 3,750 | ||||
| 7 | 28-Oct-2026 | Atlas Ltd | 2,900 |
- F2
=XMATCH(E2,B2:B7,0,-1) - G2
=INDEX(A2:A7,F2)
North Market appears three times; because the search starts from the end, the position is 5. INDEX returns the date at that position: 14-Oct-2026.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
2Pick the smallest box a product fits in
Boxes are listed by volume in column G. With match_mode 1, the next larger value is taken when there is no exact match, which gives the smallest box that fits. In column D, INDEX turns the position into a box name.
=XMATCH(B2,$G$2:$G$5,1)| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Order | Volume (L) | Box Position | Box | Box | Volume (L) | |
| 2 | SO-501 | 14 | 2 | Medium | Small | 10 | |
| 3 | SO-502 | 38 | 3 | Large | Medium | 25 | |
| 4 | SO-503 | 25 | 2 | Medium | Large | 40 | |
| 5 | SO-504 | 8 | 1 | Small | Jumbo | 60 |
- C2 (filled down to C5)
=XMATCH(B2,$G$2:$G$5,1) - D2 (filled down to D5)
=INDEX($F$2:$F$5,C2)
SO-501 needs 14 litres and does not fit the 10-litre box; mode 1 finds the next larger size, 25 litres, so D2 shows Medium. SO-503, at exactly 25 litres, gets the same box: Medium.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
Common errors and fixes
- #N/A
Cause: The value is not in the list, or approximate match found no suitable smaller or larger value (e.g. the value is larger than everything in the list with mode 1).
Fix: Check the spelling and spaces; wrap the formula in IFERROR to show a message.
- #VALUE!
Cause: One of the modes has an invalid number (e.g. match_mode 3).
Fix: Use 0, -1, 1 or 2 for match_mode and 1, -1, 2 or -2 for search_mode.
- #NAME?
Cause: The file was opened in a version without XMATCH.
Fix: Use MATCH:
=MATCH(E2,A2:A6,0).
Good to know
- The result is the position inside the range, not the row number on the sheet: in
B5:B9the first cell is 1. - To test whether a value is in a list, use
=ISNUMBER(XMATCH(E2,A2:A6)); it returns TRUE or FALSE (ISNUMBER).
In Nexell
Get Nexell to do it from a sentence
Show each customer's latest order date from the order list in a separate summary table
Convert to tableBring your order list into the tool and type this. Nexell finds each customer's last record with real Excel formulas and shows it in the preview; in the downloaded file, the summary updates when a date in the list changes.
Nexell uses AI to understand the request and builds the result with real Excel formulas; the formula may differ from the example on this page. You see a preview first and nothing changes until you press Apply; formulas stay live in the downloaded .xlsx file.
Free account · no card needed · 30 credits a month
Frequently asked questions
What is the difference between XMATCH and MATCH?
MATCH uses approximate match by default and needs a sorted list; XMATCH uses exact match by default. XMATCH can also search from the end and does not need sorting for approximate match. MATCH works in every version; XMATCH needs Microsoft 365 or Excel 2021.
Related functions
- MATCHKAÇINCI · Position of a value in a list (0 = exact match).
- XLOOKUPÇAPRAZARA · Searches an array and returns the matching item; the fourth argument if not found.
- INDEXİNDİS · Returns the value at a given row and column of a range.
- FILTERFİLTRE · Filters rows that meet a condition. Returns the third argument or #CALC! if nothing matches.