📎 This article includes 1 downloadable practice file ↓
In this article
XMATCH returns the position of an item, defaulting to exact match, and can search from the end or use wildcards.
Syntax
=XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])| Argument | What it means |
|---|---|
match_mode |
0 exact (default), −1/1 next smaller/larger, 2 wildcard. |
search_mode |
1 first-to-last, −1 last-to-first. |
Examples
Example 1
=XMATCH("Mar", B1:M1)Column position of March.
Example 2
=XMATCH(F2, A:A, 0, -1)Position of the last occurrence.
XMATCH in practice
Where you will use it
- Position of an item
- Last occurrence
- Wildcard positions
Worked example
| Find | Formula | Result |
|---|---|---|
| May | =IFERROR(XMATCH(A2,{"Apr","May","Jun"}),"not found") | 2 |
| Jun | =IFERROR(XMATCH(A3,{"Apr","May","Jun"}),"not found") | 3 |
| Dec | =IFERROR(XMATCH(A4,{"Apr","May","Jun"}),"not found") | not found |
Results calculated in Excel.
Mistakes people make
| Mistake | What to do instead |
|---|---|
| Default exact match | Unlike MATCH, XMATCH is exact by default |
| Reverse search | search_mode -1 |
Related functions
📚 Part of the free Excel course: Beginner → Expert · Try it in the Formula Lab or ask the AI Helper.
⚡ Interactive Practice Lab & Formula Tester:
Test formulas dynamically in our web formula laboratory or download the companion Excel workbook.
📎 Practice files for this article
- 📗Practice workbook: Lookup & reference17 tasks on realistic sales data — type your formula, the Check column turns green when it is right. Answers sheet included.⬇ XLSX · 35 KB
Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.
✨ Ask AI about this article
Stuck on a step? Ask a question and the AI answers using this article.
Free · AI can be wrong