The XMATCH function, the successor or MATCH function, is an in-built feature. It helps find a particular value within an array or range to come back to the relative position of stated value. Initially Microsoft brought the Excel XMATCH function as an improved version of the conventional Excel MATCH function.
Benefits of Using the XMATCH Function
By producing a user-input value to search for within a certain array, or range, the Excel XMATCH function carries out a lookup to go back to said value position. The benefits of using the Excel XMATCH function are as follows:
- Can Search horizontal and vertical arrays.
- Can Return N/A if it does not find the Lookup Value
- Can find an Exact Match while the previous one finds only an approximate match.
- Adjustment is possible for the identification of the next larger or smaller item.
- Can carry out a reverse search. For example, from the last to the first order.
- Can do a Binary search
XMATCH Function Examples
Example 1
In the following image, the XMATCH function finds the largest or an exact match value for (i.e; starts with) “Gra”
Example 2
This example finds the number of sales persons who are eligible for a bonus. It has used 1 for the match_mode to look for the next largest items in the list or an exact match. It returns a count of values due to the availability of numeric data. In the example, the function returns 4 as 4 sales representatives exceeded the bonus amount.
Excel XMATCH Formula Syntax
The Excel XMATCH function syntax is:
=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.
