The XMATCH function in Excel searches for a value in a range and returns its relative position. It is a more powerful replacement for the older MATCH function because it supports exact matching by default, reverse searches, wildcard matching, and approximate matching in either ascending or descending order. XMATCH is especially useful for lookups, dynamic formulas, and combining with INDEX to build more flexible spreadsheet solutions.
In This Guide, You’ll Learn
- What the XMATCH function does
- The syntax and arguments of XMATCH
- How to find the position of values in a list
- How to perform exact and approximate matches
- How to search from last to first using reverse matching
- Common XMATCH mistakes and real-world uses
What is the XMATCH Function?
The XMATCH function searches for a specific item in a range or array and returns the position of that item. Unlike MATCH, XMATCH defaults to exact matching and includes additional search options for greater flexibility.
XMATCH Function Syntax
=XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])Argument Definitions
- lookup_value – The value you want to find. This can be text, a number, or a cell reference.
- lookup_array – The range or array where Excel should search.
- [match_mode] – Optional. Controls how Excel matches values.
0= Exact match (default)-1= Exact match or next smaller item1= Exact match or next larger item2= Wildcard match
- [search_mode] – Optional. Controls search direction.
1= First-to-last search (default)-1= Last-to-first search2= Binary search ascending-2= Binary search descending
Example Dataset Used in This Tutorial
You can download the sample data we’ll be using in this tutorial by clicking on this link—> XMATCH Function Data
Find the Position of a Product
Suppose you have a list of products and want to find where a specific product appears in the list.
Formula Used
=XMATCH("Keyboard",A2:A6)
How This Formula Works
- Excel searches the range for Keyboard
- It returns 4 because “Keyboard” appears in the fourth position of the selected range.
Using Cell References with XMATCH
Instead of typing the lookup value directly into the formula, you can reference a cell.
Formula Used
=XMATCH(C2,A2:A6)
This approach makes your formulas dynamic. If the value in the lookup cell changes, the result automatically updates.
Approximate Match with XMATCH
You can use XMATCH to find the closest value in a sorted list.
Imagine an employee bonus structure based on sales thresholds.
Formula Used
=XMATCH(C2,A2:A6,-1)
How This Formula Works
- The formula uses -1 to return the position of the next smaller item when no exact match exists
- Since 3200 is not in the list, Excel matches it to 3000, which is the third item.
Reverse Search (Last Match)
One of XMATCH’s biggest improvements over MATCH is the ability to search from the bottom up.
Suppose an employee appears multiple times in a log and you want the most recent occurrence.
Formula Used
=XMATCH(C2,A2:A7,0,-1)
How This Formula Works
- The -1 search mode tells Excel to search from the bottom of the list upward, returning the last occurrence.
XMATCH with Wildcards
XMATCH can search using wildcard characters.
Formula Used
=XMATCH("*Juice",A2:A5,2)
How This Formula Works
- The 2 match mode enables wildcard matching.
- The asterisk (
*) represents any number of characters.
Common XMATCH Mistakes
Using an Unsorted List with Approximate Matches
Approximate matches work best when your lookup range is sorted correctly.
Forgetting XMATCH Defaults to Exact Match
Unlike MATCH, XMATCH uses exact matching automatically, which often reduces errors.
Using the Wrong Search Direction
If you want the last occurrence of a value, remember to use -1 for search mode.
Returning Position Instead of the Value
XMATCH returns a position number, not the actual value. Combine it with INDEX when you want to retrieve data.
XMATCH vs MATCH
XMATCH is generally the better choice compared to MATCH because:
- It defaults to exact matches
- It supports reverse searching
- It supports wildcard matching
- It works better with modern Excel formulas
- It is easier to understand for beginners
MATCH still works and is compatible with older Excel versions, but XMATCH offers more flexibility and fewer formula issues.
Example Dataset Used in This Tutorial
You can download the sample data we’ll be using in this tutorial by clicking on this link—> XMATCH Function Data
