Excel XMATCH function

How to use the Excel XMATCH function

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

= MATCH(E5, B6:B13, 0)    // exact match

= XMATCH(E5, B6:B13, 0)    // exact match

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:

= XMATCH(D5, B5:B12, 0)   // returns #N/A error

= XMATCH(D5, B5:B12, -1)  // returns next smaller item index

= XMATCH(D5, B5:B12, 1)   // returns next larger item index

Excel XMATCH function - Match Modes

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:

= INDEX(lookup_array,XMATCH(lookup_value,lookup_column, 0), col_ind))

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

How to use the Excel INDEX function

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.

How to use the Excel XMATCH function

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.

How to use the Excel XLOOKUP function

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.

Share on facebook
Facebook
Share on twitter
Twitter
Share on linkedin
LinkedIn
Share on reddit
Reddit
Share on whatsapp
WhatsApp
Share on email
Email
Share on facebook
Share on twitter
Share on linkedin
Share on reddit
Share on whatsapp
Share on email