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:

XMATCH Function 2 - How to use XMATCH Function in Excel

Search mode

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

XMATCH Function 3 - How to use XMATCH Function in Excel

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

XMATCH Function - How to use XMATCH Function in Excel
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:

XMATCH Function in Excel - How to use XMATCH Function in Excel

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

Syntax: =XMATCH(G2;A2:A17)
Result: 6

Syntax: =XMATCH(G3;A2:A17)
Result: 10

Syntax: =XMATCH(G4;A2:A17)
Result: 11

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:

XMATCH Function 1 - How to use XMATCH Function in Excel

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

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

Syntax: =XMATCH(G3;B2:B17;0)
Result: #N/A

Syntax: =XMATCH(G4;B2:B17;2)
Result: 5

Syntax: =XMATCH(G5;B2:B17;-1)
Result: 2

Syntax: =XMATCH(G6;B2:B17;1)
Result: 3

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:

XMATCH Function in Excel 1 - How to use XMATCH Function in Excel

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

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

Syntax: =INDEX(D2:D17;XMATCH(G3;A2:A17))
Result: 27

Syntax: =INDEX(D2:D17;XMATCH(G4;A2:A17))
Result: 50

Note:

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

Add a Comment

Your email address will not be published. Required fields are marked *