Excel XMATCH function

Description  The XMATCH function in Excel returns the relative position of a value in an array or a range of cells.

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

Lookup_value (required) – the value to look for.

Lookup_array (required) – the array or range of cells where to search.

Match_mode (optional) – specifies which match type to 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 (*, ?)

Search_mode (optional) – specifies the search direction and algorithm:

1 or omitted (default) – search from first to last.

-1 – search in reverse order from last to first.

2 – binary search ascending. Requires lookup_array to be sorted in ascending order.

-2 – binary search descending. Requires lookup_array to be sorted in descending order.

Example: