The XMATCH function in Excel is a powerful lookup function that finds the position of a value within a range or array. Unlike traditional lookup functions that return a value, XMATCH returns the relative position of the matching item. It supports exact matching, approximate matching, wildcard searches, and searching from either the beginning or the end of a range.
In this tutorial, you will learn how to use the XMATCH function in Excel through five practical examples, starting with a simple product lookup and progressing to more advanced techniques such as wildcard searches, reverse searches, and combining XMATCH with INDEX.
What Is the XMATCH Function in Excel?
XMATCH searches a range or array for a specified value and returns its relative position. It is especially useful when you need to determine where an item appears in a list.
For example, if a list contains Laptop, Monitor, Keyboard, and Mouse, XMATCH can tell you that Monitor is the second item in the list.
XMATCH Syntax
=XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])
The XMATCH function has four arguments:
- lookup_value: The value you want to find.
- lookup_array: The range or array where Excel should search.
- match_mode: Specifies how Excel should match the lookup value.
- search_mode: Specifies the direction or method used to search.
XMATCH Match Modes
| Match Mode | Description | Typical Use |
|---|---|---|
| 0 | Exact match | Find an exact value |
| -1 | Exact match or next smaller item | Approximate matching |
| 1 | Exact match or next larger item | Approximate matching |
| 2 | Wildcard match | Partial text searches |
XMATCH Search Modes
| Search Mode | Description |
|---|---|
| 1 | Search from first item to last item |
| -1 | Search from last item to first item |
| 2 | Binary search in ascending order |
| -2 | Binary search in descending order |
Example 1: Find the Position of a Product
Suppose you have a product list and want to determine the position of a specific product. XMATCH is ideal for this because it returns the position of the product in the selected range.
| A | B | C | |
|---|---|---|---|
| 1 | Product | Category | Price |
| 2 | Laptop | Electronics | 850 |
| 3 | Monitor | Electronics | 250 |
| 4 | Keyboard | Accessories | 45 |
| 5 | Mouse | Accessories | 25 |
To find the position of Monitor in the product list, use:
=XMATCH("Monitor",A2:A5)
| A | B | |
|---|---|---|
| 1 | Formula | Result |
| 2 | =XMATCH(“Monitor”,A2:A5) | 2 |
The result is 2 because Monitor is the second item within the range A2:A5.
Example 2: Find an Employee’s Position
XMATCH can also be used in employee records. For example, suppose you maintain a list of employees and need to determine the position of a particular employee in the list.
| A | B | C | |
|---|---|---|---|
| 1 | Employee | Department | Salary |
| 2 | John | Sales | 42000 |
| 3 | Sarah | Marketing | 46000 |
| 4 | David | Finance | 52000 |
| 5 | Emma | HR | 44000 |
Use the following formula to find David:
=XMATCH("David",A2:A5)
| A | B | |
|---|---|---|
| 1 | Employee | Position |
| 2 | David | 3 |
David is the third employee within the selected range, so XMATCH returns 3.
Example 3: Find the Last Occurrence of a Customer
One useful feature of XMATCH is its ability to search from the bottom of a list. This is particularly useful when a customer appears multiple times and you need to find their most recent transaction.
| A | B | C | |
|---|---|---|---|
| 1 | Customer | Date | Amount |
| 2 | ABC Ltd | 01-Jan-2026 | 1200 |
| 3 | XYZ Ltd | 03-Jan-2026 | 800 |
| 4 | ABC Ltd | 08-Jan-2026 | 1500 |
| 5 | DEF Ltd | 10-Jan-2026 | 950 |
| 6 | ABC Ltd | 15-Jan-2026 | 1800 |
To find the last occurrence of ABC Ltd, use XMATCH with search_mode set to -1:
=XMATCH("ABC Ltd",A2:A6,0,-1)
| A | B | |
|---|---|---|
| 1 | Customer | Last Position |
| 2 | ABC Ltd | 5 |
The result is 5 because the last ABC Ltd entry is the fifth item in the range A2:A6.
Example 4: Find a Product Using a Partial Name
XMATCH supports wildcard searches when match_mode is set to 2. This is useful when you know only part of a product name.
| A | B | |
|---|---|---|
| 1 | Product | Stock |
| 2 | HP Laptop 15 | 12 |
| 3 | Dell Laptop 14 | 8 |
| 4 | Lenovo Laptop 15 | 15 |
| 5 | Samsung Monitor | 10 |
Suppose you want to find the first product containing the word Laptop. Use the asterisk wildcard:
=XMATCH("*Laptop*",A2:A5,2)
| A | B | |
|---|---|---|
| 1 | Search | Position |
| 2 | *Laptop* | 1 |
XMATCH returns 1 because HP Laptop 15 is the first matching item in the selected range.
Example 5: Use INDEX and XMATCH Together
One of the most useful applications of XMATCH is combining it with INDEX. XMATCH finds the position of an item, while INDEX uses that position to return related information.
This combination can be used as a flexible lookup solution for employee records, product lists, inventory systems, customer databases, and other business data.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Employee | Department | Salary | Location |
| 2 | John | Sales | 42000 | Kathmandu |
| 3 | Sarah | Marketing | 46000 | Pokhara |
| 4 | David | Finance | 52000 | Biratnagar |
| 5 | Emma | HR | 44000 | Lalitpur |
Suppose the employee name is entered in F2 and you want to return the employee’s location.
| E | F | |
|---|---|---|
| 1 | Lookup | Result |
| 2 | David | =INDEX(D2:D5,XMATCH(E2,A2:A5)) |
The formula is:
=INDEX(D2:D5,XMATCH(E2,A2:A5))
XMATCH first finds the position of David in A2:A5. David is the third item, so XMATCH returns 3. INDEX then uses that position to return the third value from D2:D5, which is Biratnagar.
| E | F | |
|---|---|---|
| 1 | Employee | Location |
| 2 | David | Biratnagar |
XMATCH With XLOOKUP
XMATCH and XLOOKUP are often used together when you need both the position of an item and the corresponding value from another range.
For example, XMATCH can identify which row contains a product, while XLOOKUP can return its price, category, stock quantity, or other related information.
=XLOOKUP("Monitor",A2:A10,C2:C10)
If you only need the position of Monitor, XMATCH is more appropriate:
=XMATCH("Monitor",A2:A10)
XMATCH With Horizontal Data
XMATCH can search horizontally as well as vertically. This is useful when months, departments, products, or other categories are arranged across columns.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Metric | January | February | March | April |
| 2 | Sales | 12000 | 14500 | 15200 | 16800 |
To find the position of March:
=XMATCH("March",B1:E1)
The result is 3 because March is the third item in the selected horizontal range.
How to Handle #N/A Errors With XMATCH
If XMATCH cannot find the requested value, it returns the #N/A error. You can use IFNA to display a more useful message.
=IFNA(XMATCH("Printer",A2:A10),"Product not found")
If Printer does not exist in the range, Excel will display Product not found instead of #N/A.
Common XMATCH Mistakes
1. Forgetting That XMATCH Returns a Position
XMATCH returns a position, not the value from another column. If you need to return related information, consider combining XMATCH with INDEX or using XLOOKUP.
2. Using the Wrong Match Mode
For normal exact searches, use match_mode 0. If you want wildcard matching, use 2.
3. Confusing Search Mode With Match Mode
Match mode controls how the value is matched. Search mode controls the direction or method used to search.
4. Searching the Wrong Range
Make sure the lookup value and lookup array correspond to the data you actually want to search.
XMATCH vs MATCH
| Feature | XMATCH | MATCH |
|---|---|---|
| Returns position | Yes | Yes |
| Exact match | Yes | Yes |
| Wildcard matching | Yes | Limited |
| Search from bottom | Yes | No |
| Next larger/smaller match | Yes | Yes |
For modern Excel versions, XMATCH is generally the more flexible choice when you need to find a position in a range.
When Should You Use XMATCH?
XMATCH is particularly useful when you need to:
- Find the position of a product in an inventory list.
- Locate an employee in a database.
- Find the latest occurrence of a customer.
- Search for partial text using wildcards.
- Build flexible INDEX and XMATCH lookup formulas.
- Work with horizontal or vertical lists.
- Determine the position of a month, department, product, or category.
- Create dynamic lookup systems in Excel reports and dashboards.
XMATCH Quick Reference
| Task | Formula | Purpose |
|---|---|---|
| Exact match | =XMATCH(“Monitor”,A2:A10) | Find exact position |
| Reverse search | =XMATCH(“ABC”,A2:A10,0,-1) | Find last occurrence |
| Wildcard search | =XMATCH(“*Laptop*”,A2:A10,2) | Find partial text |
| INDEX + XMATCH | =INDEX(C2:C10,XMATCH(E2,A2:A10)) | Return related value |
| Handle missing value | =IFNA(XMATCH(E2,A2:A10),”Not found”) | Display custom message |
Frequently Asked Questions About XMATCH
Conclusion
The XMATCH function in Excel provides a flexible way to find the position of an item within a range or array. It supports exact matching, approximate matching, wildcard searches, and reverse searches, making it more versatile than the traditional MATCH function.
For simple position searches, XMATCH can be used by itself. For more advanced lookup tasks, combining XMATCH with INDEX provides a powerful and flexible solution. Understanding these techniques can help you build more dynamic Excel reports, inventory systems, employee databases, sales reports, and business dashboards.
