Lookup & Reference
Excel 2021
◉ 1 views
=XMATCH()
Finds the position (relative row or column number) of a value in a range or array; the modern, more complete version of MATCH
=XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])
Hover argument names in the table for details.
Output typeNumber
Popularity70 / 100
VolatileNo
Dynamic ArrayNo
📥 Download sample .xlsx — Coming soon
▦ Try in Playground
Dedicated file for this function — safe & lightweight · sample data + live formulas
⬇ Download Sample Workbook
A dedicated .xlsx for =XMATCH() with Persian sample data & live formulas — open in Excel and practice.
- Sheet “Sample” — data table + formula in place
- Sheet “Guide” — syntax, arguments & tips from this page
- Instant & secure (token + rate limit)
🧠 🧠 Quiz this function
%d questions — syntax, example & errors — test yourself!
◧ Arguments
| Argument | Type | Required | Description |
|---|---|---|---|
lookup_value |
Any type | Required | Value to search for |
lookup_array |
Range | Required | Range or array to search in (only one column or one row) |
match_mode |
Number | Optional | Match type — default: 0 |
▣ Examples
Example 1
=XMATCH("Keyboard", B2:B10)
Example 2
=INDEX(C2:C10, XMATCH(F2, A2:A10))
Example 3
=INDEX(B2:E10, XMATCH(H1, A2:A10), XMATCH(H2, B1:E1))
Example 4
=XMATCH(F2, A2:A100, 0, -1)
✦ Tips
- The lookup range must be only one column or only one row; a multi-column and multi-row range returns the #VALUE! error.
- The output is the relative position within the range, not the sheet row number.
- Not case-sensitive for Latin letters.
- In binary search (2 and -2) the data must be sorted; otherwise an incorrect result is returned without any error.
- To search for the literal characters * and ? in wildcard mode, place a ~ before them.
- If the first argument is a range or array, a position is returned for each value and the result spills.
- Regex mode (value 3 for the third argument) is only available in newer Microsoft 365 versions.
⚠ Common Errors
#N/A
The desired value was not found in the lookup range (usually due to extra spaces or mismatched number vs. text data types)
Fix: Clean the data with TRIM, ensure consistent data types, and handle the error with IFNA if needed
💬 Comments & Q&A 0 approved
🔒 Your comment will be published after review.Share your thoughts — visible after admin approval
No comments yet — be the first!