The Excel XMATCH dynamic function searches for a specified item in vertical or horizontal ranges. It is more flexible than the MATCH function because it supports exact and approximate matching, reverse search, and first-to-last or last-to-first search, and it also uses a faster binary search algorithm.

XMATCH requires four arguments:

• lookup_value—it contains the value you want to look up.
• lookup_array—it identifies the array or range of cells.
• Match_mode—it represents how good of a match you want a result to be, where 0 = exact match, -1 = exact match or next smallest, 1 = exact match or next larger, and 2 = wildcard match.
• search_mode—it specifies the type of search you want to perform, where 1 = search from first (default), -1 = search from last, 2 = binary search ascending, and -2 = binary search descending.

Suppose you have a database spreadsheet, containing the names of all sales representatives in your company. Now, you want to find the number of salespeople eligible for a bonus. Thanks to XMATCH, this is a straightforward task.

You simply pick the bonus eligibility amount, then select the range of sales for each sales representative, and choose 1 for the match_mode to obtain an exact match or the next largest item in the list. Since the data is numeric, the function returns a count of values. In our example, the function returns 3— there are 3 sales representatives, eligible for a bonus.

This open-access Excel template is a useful tool for financial analysts, business analysts, consultants, corporate executives, and anyone preparing a corporate presentation.

Discover the power of Excel and boost your skills with our comprehensive Introduction to Excel course.

You can also explore other related functions such as—SORTBY, SEQUENCE, RANK, and FREQUENCY.