How to use XMATCH Function in Excel

The Excel XMATCH function performs a lookup and returns a position in vertical or horizontal ranges. It is a more robust and flexible successor to the MATCH function. XMATCH supports approximate and exact matching, reverse search, and wildcards (* ?) for partial matches.

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

The XMATCH function syntax has the following arguments:

• lookup_value – The lookup value.
• lookup_array – The array or range to search.
• match_mode – [optional] 0 = exact match (default), -1 = exact match or next smallest, 1 = exact match or next larger, 2 = wildcard match.
• search_mode – [optional] 1 = search from first (default), -1 = search from last, 2 = binary search ascending, -2 = binary search descending.

Match type

The third argument for XLOOKUP is match_type. This is an optional argument that controls match behavior as follows:

Search mode

The fourth argument for XLOOKUP is search_mode. This is an optional argument that controls search behavior as follows:

Example: Let’s look at some Excel XMATCH function examples and explore how to use the XMATCH function as a worksheet function in Microsoft Excel:Binary searches are very fast, but take care data is sorted as required. If data is not sorted properly, a binary search can return invalid results that look perfectly normal.

Example 1: Exact match

In the example shown, XMATCH is used to retrieve the position in a list of  the range A2:A17. The formula is:

Syntax:  =XMATCH(G2;A2:A17)

Result:

Based on the Excel spreadsheet above, the following XMATCH examples would return:

Notice XMATCH defaults to an exact match. If “Jack Albertson” G2 was misspelled “Jack Albertsol”, XMATCH would return #N/A.

Example 2: Match behavior

Syntax: =XMATCH(G2;B2:B17;0)

Result:

Based on the Excel spreadsheet above, the following XMATCH examples would return:

Example 3: INDEX and XMATCH
XMATCH can be used just like MATCH with the INDEX function. To retrieve the age of Jack Albertson based on the original example above, the formula is:

Syntax: =INDEX(D2:D17;XMATCH(G2;A2:A17))

Result:

Based on the Excel spreadsheet above, the following XMATCH examples would return:

Note:

1. XMATCH can work with both vertical and horizontal arrays.
2. XMATCH will return #N/A if the lookup value is not found.