background
Welcome to Wall Street Prep! Use code at checkout for 15% off.
Wharton & Wall Street Prep Certificates
Now Enrolling for September 2024 for September 2024
:
Private EquityReal Estate Investing
Buy-Side InvestingFP&A
Wharton & Wall Street Prep Certificates:
Enrollment for September 2024 is Open
Wall Street Prep

XMATCH Function

Step-by-Step Guide to Understanding the XMATCH Function in Excel

Last Updated November 12, 2023

Learn Online Now

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

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

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.

Alt MOClick “XMATCH”
Once pressed, the following table should appear on the screen, where the function arguments are explicitly stated.

Excel XMATCH Function Syntax Arguments

Excel XMATCH Calculator

We’ll now move to a modeling exercise, which you can access by filling out the form below.

dl

Get the Excel Template!

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

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.

Item Q1-2023 Q2-2023 Q3-2023 Q4-2023
Product A $140k $150k $160k $180k
Product B $200k $220k $225k $250k
Product C $120k $125k $150k $160k
Product D $100k $110k $120k $125k
Product E $180k $185k $190k $200k
Product F $160k $165k $180k $185k

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.

=INDEX(C6:F11,XMATCH(J8, B6:B11),XMATCH(J9,C5:F5))

XMATCH Multiple Criteria Calculation Example

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

Excel XMATCH Calculator

Turbo-charge your time in Excel Used at top investment banks, Wall Street Prep's Excel Crash Course will turn you into an advanced Power User and set you apart from your peers.
Learn More

Comments
0 Comments
Inline Feedbacks
View all comments
Learn Excel Online

Become an Excel “Power User.” Used to train new hires at top financial institutions around the world!

Learn More

The Wall Street Prep Quicklesson Series

7 Free Financial Modeling Lessons

Get instant access to video lessons taught by experienced investment bankers. Learn financial statement modeling, DCF, M&A, LBO, Comps and Excel shortcuts.