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

Remarks

* This function is only available in Microsoft 365.
* 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

 ABCDE
1=XMATCH(10,B1:B7) = 2535AppleStrawberry
2=XMATCH(10,B1:B7,0) = 21030BananaPeach
3=XMATCH("Cherry",D1:D7) = 31525CherryOrange
4=XMATCH(13,B1:B7) = #N/A2020LemonLemon
5=XMATCH("Cherrys",D1:D7) = #N/A2515OrangeCherry
6=XMATCH(13,B1:B7,1) = 33010PeachBanana
7=XMATCH("Cherrys",D1:D7,1) = 4355StrawberryApple
8=XMATCH(13,C1:C7,-1) = 6    
9=XMATCH("Cherrys",E1:E7,-1) = 5    
10=XMATCH(2,B1:B7,-1) = #N/A    
11=XMATCH(40,B1:B7,-1) = 7    
12=XMATCH(2,C1:C7,1) = 7    
13=XMATCH(40,C1:C7,1) = #N/A    
14=XMATCH(20,B1:B7,10) = #VALUE!    
15=XMATCH(2,{1,2;3,4;5,6}) = #VALUE!    
16=XMATCH("Cherry",D1:E2) = #VALUE!    

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 multi-dimensional 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).

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