Home › Functions › Lookup & Reference › =XMATCH()
Lookup & Reference Excel 2021 ◉ 23 views

=XMATCH()

The XMATCH function searches for a specified item in an array or range of cells, and then returns the relative position of the first match.

Applies To: Excel for Microsoft 365Excel for Microsoft 365 for MacExcel 2024Excel 2024 for MacExcel 2021Excel 2021 for Mac
=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 ▦ Try in Playground Dedicated file for this function — safe & lightweight · sample data + live formulas
Share:

⬇ 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)
Download XMATCH.xlsx ~ 8–12 KB · No macros

⇪ Share

Send this function to friends

Quiz this function

%d questions — syntax, example & errors — test yourself!

◧ Arguments

ArgumentTypeRequiredDescription
lookup_value Any type Required The lookup value
lookup_array Range Required The array or range to search
match_mode] Number Optional Specify the match type:0 - Exact match (default)-1 - Exact match or next smallest item1 - Exact match or next largest item2 - A wildcard match where *, ?, and ~ have special meaning.
search_mode] Range Optional Specify the search type:1 - Search first-to-last (default)-1 - Search last-to-first (reverse search).2 - Perform a binary search that relies on lookup_array being sorted in ascending order. If not sorted, invalid results will be returned.-2 - Perform a binary search that relies on lookup_array being sorted in descending order. If not sorted, invalid results will be returned.

▣ Examples

Example 1 =XMATCH(F2,C3:C9,1)
Example 2 =INDEX(C6:E12,XMATCH(B3,B6:B12),XMATCH(C3,C5:E5))
Example 3 =XMATCH(4,{5,4,3,2,1})
Example 4 =XMATCH(4.5,{5,4,3,2,1},1)

⇄ Related Functions

=INDEX() Returns the value of an element in a table or an array, selected by the row and column number indexes. Use the array form if the first argument to INDEX is an array constant. Returns the reference of the cell at the intersection of a particular row and column. If the reference is made up of non-adjacent selections, you can pick the selection to look in. =TRIM() Removes all spaces from text except for single spaces between words. Use TRIM on text that you have received from another application that may have irregular spacing. =MATCH() How to use the MATCH function in Excel to search for a specified item in a range of cells, returning the relative position of that item in the range. =XLOOKUP() Use the XLOOKUP function when you need to find things in a table or a range by row. For example, look up the price of an automotive part by the part number, or find an employee name based on their employee ID. With XLOOKUP, you can look in one column for a search term, and return a result from the same row in another column, regardless of which side the return column is on.

💬 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!

✍️ Add comment / question

captcha
🔒 Your comment will be published after review.