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: 0 (False) = Exact match, lookup_array does not have to be sorted (default) 1 = Closest match, exact or the next smallest 1 (True) = Closest match, exact match or the next largest 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 1 and 1 is now 1. * Wildcard character: ? = a single character. * Wildcard character: * = multiple characters. * To include the actual wildcard characters use a tilde prefix (~?), (~*) and (~~). * If "lookup_value" cannot be found and "match_mode" = 0, then #N/A is returned. * If "lookup_value" cannot be found and "match_type" = 1, then the next smallest value is returned. * If "lookup_value" cannot be found and "match_type" = 1, then the next largest value is returned. * If "lookup_array" is a twodimensional range and not a single column (or row) reference, then #VALUE! is returned. * If "match_mode" is left blank, then 0 is used. * If "match_type" = 1 then "lookup_array" must be sorted into ascending order (1, 0, 1, a, A, FALSE, TRUE). * If "match_type" = 1 then "lookup_array" must be sorted into descending order (TRUE, FALSE, A, a, 1, 0, 1). * 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. * For the Microsoft documentation refer to support.microsoft.com * For the Google documentation refer to support.google.com 

1  Find the position of the value "Cc" in column "B". 2  Find the position of the value "Aa" in column "B". 3  Find the position of the value "AA" in column "B". This function is not case sensitive. 4  Find the position of the value "AA" in column "B" with a case sensitive match. Instead of True you could also use 1. 5  Find the position of the value "NotFound" in column "B". The default is an exact match. 6  Find the position of the closest match (or next smallest) to the value "C" in column "B". Next smallest is "Ab". 7  Find the position of the closest match (or next smallest) to the value "C" in column "B". The list is not sorted. 8  Find the position of the closest match (or next largest) to the value "A" in column "B". Next largest is "Aa". 9  Find the position of the closest match (or next largest) to the value "A" in column "B". The list is not sorted. 10  Find the position of the first match to the value "C*" in column "B". This is the first match. 
© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited Top