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

Returns the position of a value in a list, table or cell range.

lookup_valueThe value you want to look for.
lookup_arrayThe array of values that contain the value you want to look for.
match_mode(Optional) The match mode to use:
0 = Exact match is returned (lookup_array does not have to be sorted) (default)
-1 = Exact match or the next smallest item
1 = Exact match or the next largest item
2 = Wildcard character match (? and *)
search_mode(Optional) The search mode to use:
1 = First to Last (default)
-1 = Last to First
2 = First to Last (case sensitive) lookup_array must be sorted in ascending order
-2 = Last to First (case sensitive) lookup_array must be sorted into descending order


* This function is only available in Office 365 to Office Insiders at the moment.
* For an illustrated example refer to the page under Lookup Functions.
* This function replaces the MATCH function.
* This function supports wildcards (? or *).
* Wildcard character: ? = a single character.
* Wildcard character: * = multiple characters.
* You can use the XLOOKUP function to return the value in the same row (or column) after finding a matching value in any column.
* For the Microsoft documentation refer to support.office.com

1=XMATCH(2,{1,2,3}) = 2

© 2019 Better Solutions Limited. All Rights Reserved. © 2019 Better Solutions Limited Top