Description
The Excel XMATCH function searches for a specific item in a given range and returns the position as an index number. XMATCH is a more flexible and robust version of the MATCH function. XMATCH covers exact, approximate, and wildcard matching plus it also offer reverse search. Just like the MATCH function, XMATCH can be used together with the INDEX function for powerful and version-independent lookup operations.
Syntax
XMATCH( lookup_value, lookup_array, [match_mode], [search_mode] )
Arguments
lookup_value – The value you want to search for
lookup_array – A range of cells or array
match_mode – [Optional] A value indicating which match mode to use
search_mode – [Optional] A value indicating which search mode to use
Return value
An integer number or array representing the position of the lookup_value
How to use the XMATCH function in Excel
The Excel XMATCH function performs a lookup and returns a position as an index number. XMATCH is considered to be a more flexible and robust successor of the MATCH function.
XMATCH performs both vertical and horizontal lookups and covers exact, approximate, and wildcard matching. In addition, it also offers various advanced search modes, like reverse search or binary search (optimised for speed).
For simple use cases with exact match (0), you can replace MATCH by XMATCH without changing any part of the syntax:wit
However, you have to be careful whenever you apply approximate or wildcard match. For these match modes, XMATCH has the following behaviour for the different keys:
Match Type | Match Behaviour |
|---|---|
0 (default) | Exact match only (returns #N/A for no matcH) |
-1 | Exact match or next smaller item |
1 | Exact match or next larger item |
2 | Wildcard Match (*,?,~) |
When we compare these match type keys to the MATCH function, the keys for approximate match (-1 and 1) are reversed and wildcard match now has its own keys (for MATCH it was covered by key 0).
Match Mode Comparison
Let’s take a closer look at the different match modes with the lookup value 518 that doesn’t exactly match any of the values in the lookup_array.
In the example below, you can see the first three match modes side by side with the following formulas:
Note: Wildcard match (match_mode = 2) is only recommended for text string values.
Advanced Search Modes
Unlike the simple MATCH function, XMATCH offers a wide range of alternative search modes. Here is a list of all search mode keys you can enter for the optional search_mode argument:
Search Mode | Search Behaviour |
|---|---|
1 (default) | Search from first value |
-1 | Reversed search from last value |
2 | Binary search with values sorted in asc order |
-2 | Binary search with values sorted in desc order |
Binary search is faster compared to normal and reversed search, but it requires you to sort the data first. For a more robust application of XMATCH, we recommend to focus on the normal and reversed order search modes (1 and -1).
INDEX and XMATCH
The Excel XMATCH function can be used together with the INDEX function just like MATCH. In this combination, XMATCH covers the task of locating and feeding a position to the INDEX function, which then returns the value at that given position. The basic syntax of INDEX MATCH looks like this:
In the above statement, XMATCH locates the position of a lookup_value in the lookup_column, which is then used as the row_num index in the INDEX function. With a given lookup_array and col_ind for the result column, INDEX is then able to perform a flexible lookup that is even superior to the VLOOKUP function.
Additional Notes
If no match is found, MATCH throws an #N/A error. You can trap this error by wrapping MATCH with the IFNA function.
Also, be aware that XMATCH is only available to users of Excel 365. If you share your file with other people, it is more safe to rely on the MATCH function which is supported across all Excel versions.
Formula examples
Related Tutorials
Related Functions
Excel XMATCH function
The Excel XMATCH function searches for a specific item in a given range and returns the position. XMATCH is a more flexible and robust version of MATCH.
Excel XLOOKUP function
The Excel XLOOKUP function searches an array, and returns an item corresponding to the first match it finds. It replaces many older lookup functions.
Excel INDEX function
The Excel INDEX function returns the value of an element in a range or array at a given position, specified by the row and column number indexes.
