How to Use XMATCH in Excel
The Excel XMATCH Function searches for a specified value in a selected array or range of cells to return its relative position.
The XMATCH function in Excel is a tool used to perform a “lookup” and is considered an enhancement from its predecessor, the MATCH function, to offer more capabilities and improved flexibility.
What is XMATCH in Excel?
The XMATCH function in Excel is a built-in feature used to search for a specified value within a range, or array, to return the relative position of said value.
Initially, Microsoft introduced the Excel XMATCH function as an improvement from the traditional Excel MATCH function.
Given a user-input value to search for within a specified range, or array, the Excel XMATCH function performs a “lookup” to return the position of the value.
The practical use-case of the XMATCH function in Excel is to identify the relative positioning of a user-input value within a data set.
The versatility of the XMATCH function is that the function can be used for a broad range of purposes, including matching data across multiple columns or performing a search with multiple criteria.
Often, the more advanced techniques require combining the XMATCH function with other functions, most often XLOOKUP, INDEX, and CHOOSE.
The other benefits of using the XMATCH function include the following:
- Can Search Vertical and Horizontal Arrays
- Can Return “N/A” if the Lookup Value Cannot Be Found
- Searches for an Exact Match by Default, while MATCH Resorts to Finding an Approximate Match
- Can Be Adjusted to Identify the Next Larger Item or the Next Smaller Item
- Can Conduct a Reverse Search – i.e. Last to First Order
- Capable of Performing a Binary Search
Compared to the MATCH function, the underlying mechanism of the XMATCH function is relatively similar, but there is far more optionality and flexibility in the latter.
Note: The XMATCH function is available only for Excel in Microsoft 365 and Excel 2021.
Excel XMATCH Function – Case-Sensitive Error
One common mistake regarding XMATCH stems from the fact that the function is case-insensitive.
In effect, the function will not differentiate between uppercase and lowercase letters while searching for a match, which must be understood to use the function properly.
Excel XMATCH Formula Syntax
The syntax for the XMATCH function in Excel is as follows.
- Lookup_value → The specified value to search for.
- Lookup_array → The chosen array or range of cells to search for the specified value.
- Match_mode → The specified match type that the function should use.
- 0 or Omitted (Default) → Exact Match
- -1 → Exact Match or the Next Smallest Value
- 1 → Exact Match or the Next Largest Value
- 2 → Wildcard Match – i.e. Partial Matches (e.g. “*”, “?”)
- Search_mode → The specified sequence of the search (i.e. the order, or direction of the sort)
- 1 or Omitted (Default) → First to Last
- -1 → Last to First → The reverse of the default (last to first, instead of first to last)
- 2 → Binary Search Ascending → The lookup_array is sorted in ascending order.
- -2 → Binary Search Descending → The lookup_array is sorted in descending order.
The only required inputs for the XMATCH function to work are the initial two – i.e. the “lookup_value” and the “lookup_array” – while the other remaining inputs are optional (and can be omitted).
The brackets enclosed around “match_mode” and “search_mode” denote that the inputs are optional.
What is the Keyword Shortcut to Open XMATCH?
The shortcut to open the XMATCH function box is as follows.
Excel XMATCH Calculator
We’ll now move to a modeling exercise, which you can access by filling out the form below.
Multiple Criteria XMATCH Calculation Example
Suppose we’re tasked with using the XMATCH function in Excel to determine the quarterly revenue generated by a company in Q4-2023.
For fiscal year 2023, the company reported the following quarterly revenue figures per quarter.
By pressing “Alt” → “A” → “V”, we can open the data validation tool, where we’ll create a list to select the product (and repeat the process to select the quarter).
While there are various methods to achieve the outcome that we’re pursuing, the two functions that we’ll use here are XMATCH and INDEX.
In conclusion, once we select the appropriate cases from the drop-down boxes – i.e. “Product D” and “Q4-2023” – the output from the XMATCH function in Excel is $125k.
- Revenue (Q4-2023) = $125k