XMATCH Function

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 Formula Syntax

The syntax for the XMATCH function in Excel is as follows.

Where:

• 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.

Get the Excel Template!

First Name *

Email *

Submit

By submitting this form, you consent to receive email from Wall Street Prep and agree to our terms of use and privacy policy.

Email provided

Your Download is Ready

Submitting...

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