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 closest value less than it 1 (True) = Closest match, exact match or the 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 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 two-dimensional 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. * 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 * For the Google documentation refer to support.google.com |
|
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. |
© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited Top