Find Anything, Anywhere: A Guide to Excel's XMATCH Function

The new, more flexible alternative to the classic MATCH function is here.

Welcome! If you’ve ever used Excel’s MATCH function, you know it’s a crucial tool for finding the position of an item in a list. But with the introduction of the **XMATCH** function (available in Microsoft 365 and Excel 2021), we now have a more powerful, flexible, and intuitive alternative. XMATCH is the new standard for finding a value's relative position in an array or range. Let's see how it works.

Why XMATCH is Better

The traditional MATCH function had limitations, especially with approximate matches and searching in reverse. XMATCH solves these problems and adds new capabilities:

• **Exact Match by Default:** Unlike MATCH, which defaults to an approximate match, XMATCH defaults to an exact match (`match_mode` 0), which is what most users need most of the time.
• **Search from Last to First:** With a simple argument (`search_mode`), you can easily search from the end of a list to the beginning.
• **Binary Search:** For large, sorted datasets, XMATCH offers a super-fast binary search option.

The XMATCH Function Syntax

The syntax for `XMATCH` is simple and follows the same logical pattern as other modern Excel functions:

Syntax: =XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])

• lookup_value (Required): The value you want to find.
• lookup_array (Required): The range or array of cells where you want to search.
• match_mode (Optional):
  • `0` (default) - Exact match.
  • `-1` - Exact match or next smallest item.
  • `1` - Exact match or next largest item.
  • `2` - Wildcard match (`*` and `?`).
• search_mode (Optional):
  • `1` (default) - Search from first to last.
  • `-1` - Search from last to first (reverse).
  • `2` - Binary search (ascending sort).
  • `-2` - Binary search (descending sort).

Putting It to Use: Practical Example

Imagine you have a list of sales data and you want to find the position of a specific sales person in that list. Here's how you'd use XMATCH for an exact match:

=XMATCH("Jane Smith", A2:A100)
            

This simple formula will return the relative position of "Jane Smith" in the range A2:A100. If she is the 5th person in the list, the formula will return `5`. XMATCH is also a perfect partner for the `INDEX` function, allowing you to perform powerful lookups that were once complicated.

By making the switch to XMATCH, you'll be able to perform lookups with more precision and flexibility, making your data analysis more robust and efficient.

Continue your Excel journey with more powerful formula tips!