XMATCH

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

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

lookup_value	The value you want to look for.
lookup_array	The array of values that contain the value you want to look for.
match_mode	(Optional) A number indicating the type of match: -1 = Closest match, exact or closest value less than it 0 (False) = Exact match, the lookup_array does not have to be sorted (default) 1 (True) = Closest match, exact or closest value greater than it 2 = Wildcard character match (? and *)
search_mode	(Optional) A number indicating the search mode: 1 = Linear search, from first to last (default) -1 = Linear search, from last to first 2 = Binary search, lookup_array must be sorted into ascending order -2 = Binary search, lookup_array must be sorted into descending order

REMARKS

* For an illustrated example refer to the page under Lookup Functions and MATCH vs XMATCH
* This function was added in Excel 2021 to replace the MATCH function.
* This function is not case sensitive when matching text strings.
* This function supports wildcards (? and *).
* Be careful if upgrading from MATCH because the 'match_mode' numbers are different, -1 is now less than and 1 is now greater than.
* Wildcard character: ? = a single character.
* Wildcard character: * = multiple characters.
* To include the actual wildcard characters use a tilde prefix (~?), (~*) and (~~).
* If 'lookup_array' is a two-dimensional range and not a single column (or row) reference, then #VALUE! is returned.
* If 'match_mode' = -1 and 'lookup_value' cannot be found, the closest value less than it is used.
* If 'match_mode' = -1 then 'lookup_array' must be sorted into ascending order (-1, 0, 1, a, A, FALSE, TRUE).
* If 'match_mode' = 0 and 'lookup_value' cannot be found, then #N/A is returned.
* If 'match_mode' = 0, then 'lookup_array' does not have to be sorted.
* If 'match_mode' = 1 and 'lookup_value' cannot be found, the closest value greater than it is used.
* If 'match_mode' = 1 then 'lookup_array' must be sorted into descending order (TRUE, FALSE, A, a, 1, 0, -1).
* If 'match_mode' is left blank, then 0 is used.
* If 'match_mode' is not 0,1,-1,2,True or False, then #VALUE! is returned.
* If 'search_mode' is left blank, then 1 is used.
* You can use the XLOOKUP function to return a value in the same row (or column).
* You can use the VLOOKUP function to return a value in the same row.
* You can use the HLOOKUP function to return a value in the same column.
* You can use the LOOKUP function to return a value in a row (or column) that matches a value in a column (or row).
* You can use the INDEX function to return the intersection of a row and a column.
* You can use the OFFSET function to return a value in an offset position.
* You can use the CHOOSE function to return the value in a list based on an index number.
* You can use the EXACT function to perform a case sensitive match.
* You can use the IFNA function to return the value or something else if it evaluates to #N/A.
* For the Microsoft documentation refer to support.microsoft.com

	A	B
1	=XMATCH("Cc", B1:B3) = 3	Aa
2	=XMATCH("Aa", B1:B3) = 1	Ab
3	=XMATCH("AA", B1:B3) = 1	Cc
4	=XMATCH(TRUE, EXACT(B1:B3, "AA")) = #N/A
5	=XMATCH("NotFound", B1:B3) = #N/A	Ab
6	=XMATCH("C", B1:B3, -1) = 2	Cc
7	=XMATCH("C", B5:B7, -1) = 1	Aa
8	=XMATCH("A", B5:B7, 1) = 3
9	=XMATCH("B*", B1:B3, 2) = #N/A

1 - Find the position of the value "Cc" in the range "B1:B3".
2 - Find the position of the value "Aa" in the range "B1:B3".
3 - Find the position of the value "AA" in the range "B1:B3". This function is not case sensitive.
4 - Find the position of the value "AA" in the range "B1:B3" with a case sensitive match. Instead of True you could also use 1.
5 - Find the position of the value "NotFound" in the range "B1:B3". The default is an exact match.
6 - Find the position of the closest match (or next smallest) to the range "B1:B3". Next smallest is "Ab".
7 - Find the position of the closest match (or next smallest) to the value "C" in range "B5:B7". The list is not sorted.
8 - Find the position of the closest match (or next largest) to the value "A" in the range "B5:B7". The list is not sorted.
9 - Find the position of the first match to the value "C*" in the range "B5:B7". This is the first match.