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) 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 
Remarks 
* This function is only available in Microsoft 365 and was released in February 2020. * For an illustrated example refer to the page under Lookup Functions. * There is also a page under Lookup Functions of MATCH vs XMATCH * This function replaces the MATCH function. * The MATCH function has different "match_mode" numbers, 1 is now 1 and 1 is now 1. * This function is not case sensitive by default but this can be changed using the "search_mode" argument. * This function supports wildcards (? and *). * Wildcard character: ? = a single character. * Wildcard character: * = multiple characters. * If "lookup_value" cannot be found and "match_mode" = 0, then #N/A is returned. * If "lookup_value" cannot be found and "match_mode" = 1 it uses the largest value that is less than the "lookup_value". * If "lookup_value" cannot be found and "match_mode" = 1 it uses the smallest value that is greater than the "lookup_value". * You can use the XLOOKUP function to return a value in the same row (or column). * 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. * For the Microsoft documentation refer to support.office.com 

1  What is the position of the value 10 in the sorted block of cells. 2  What is the position of the value 10 in the sorted block of cells. The default "match_mode" is 0. 3  What is the position of the item "Cherry" in the sorted block of cells. 4  The default "match_mode" is 0 and because the "lookup_value" does not exist, #N/A is returned. 5  The default "match_mode" is 0 and because the "lookup_value" does not exist, #N/A is returned. 6  The "match_mode" is 1 so it returns the position of the smallest value greater than 13, which is 15 (position 3) (list in ascending order). 7  The "match_mode" is 1 so it returns the position of the smallest value that is greater than "Cherrys", which is "Lemon" (position 4). 8  The "match_mode" is 1 so it returns the position of the largest value less than 13, which is 10 (position 6) (list in descending order). 9  The "match_mode" is 1 so it returns the position of the largest value less than "Cherrys" which is "Cherry" (position 5). 10  The "match_mode" is 1 and because the "lookup_value" is smaller than the first item, #N/A is returned. 11  The "match_mode is 1 and because the "lookup_value" is greater than the last item, 7 is returned. 12  The "match_mode is 1 and because the "lookup_value" is smaller than the last item, 7 is returned. 13  The "match_mode is 1 and because the "lookup_value" is greater than the first item, #N/A is returned. 14  If the "match_mode" > 1, then #VALUE is returned (The MATCH function assumes its 1). 15  If "lookup_array" is a multidimensional array, then #VALUE! is returned (The MATCH function returns #N/A). 16  If "lookup_array" refers to multiple rows or columns, then #VALUE! is returned (The MATCH function returns #N/A). 
© 2021 Better Solutions Limited. All Rights Reserved. © 2021 Better Solutions Limited Top