MATCH |
MATCH(lookup_value, lookup_array [,match_type]) |
Returns the position of a value in a list, table or cell range. |
lookup_value | The value you want to find. |
lookup_array | The single column (or single row) of cells containing possible lookup values. |
match_type | (Optional) The number specifying the type of match performed: 0 = Exact match is returned -1 = Exact match or the next largest item 1 = Exact match or the next smallest item (default) |
REMARKS |
* For an illustrated example refer to the page under Lookup Functions * XMATCH was added in Microsoft 365 to replace this function. * Before you upgrade to XMATCH you need to read MATCH vs XMATCH * The XMATCH function has different "match_type" numbers, -1 is now 1 and 1 is now -1. * This function is not case sensitive when searching for text strings. * This function supports wildcards (? and *). * Wildcard character: ? = a single character. * Wildcard character: * = multiple characters. * The "lookup_value" is the value you want to match in the "lookup_array". * The "lookup_value" can be a value (number, text, or logical value) or a cell reference. * If "lookup_value" is text, then it can include the two wildcard characters. * If "lookup_value" cannot be found and "match_type" = 0, then #N/A is returned. * If "lookup_value" cannot be found and "match_type" = -1, it uses the smallest value that is greater than the "lookup_value". * If "lookup_value" cannot be found and "match_type" = 1, it uses the largest value that is less than the "lookup_value". * The "lookup_array" can be a cell reference or an array. * If "lookup_array" references more than a single column of cells, then #N/A is returned. * If "lookup_array" references more than a single row of cells, then #N/A is returned. * If "lookup_array" references a multi dimensional array, then #N/A is returned. * If "match_type" is left blank, then 1 is used. * If "match_type" = -1, then "lookup_array" must be sorted into descending order (TRUE, FALSE, A, a, 1, 0, -1). * If "match_type" = 1, then "lookup_array" must be sorted into ascending order (-1, 0, 1, a, A, FALSE, TRUE). * If "match_type" = 0, then "lookup_array" does not have to be sorted. * If "match_type" < 0, then, -1 is used. * If "match_type" > 0, then 1 is used. * You can use the XMATCH function to return the position of a value. * You can use the HLOOKUP function to return a value in the same column. * You can use the VLOOKUP function to return a value in the same row. * 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.microsoft.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_type" is 1. 3 - What is the position of the item "Cherry" in the sorted block of cells. 4 - The default "match_type" is 1 so it returns the position of the largest value less than 13, which is 10 (position 2) (list in ascending order). 5 - The default "match_type" is 1 so it returns the position of the largest value less than "Cherrys" which is "Cherry" (position 3). 6 - The "match_type" is 0 and because the "lookup_value" does not exist, #N/A is returned. 7 - The "match_type" is 0 and because the "lookup_value" does not exist, #N/A is returned. 8 - The "match_type" is -1 so it returns the position of the smallest value greater than 13, which is 15 (position 5) (list in descending order). 9 - The "match_type" is -1 so it returns the position of the smallest value greater than "Cherrys" which is "Lemon" (position 4). 10 - The "match_type" is 1 and because the "lookup_value" is smaller than the first item, #N/A is returned. 11 - The "match_type is 1 and because the "lookup_value" is greater than the last item, 7 is returned. 12 - The "match_type is -1 and because the "lookup_value" is smaller than the last item, 7 is returned. 13 - The "match_type is -1 and because the "lookup_value" is greater than the first item, #N/A is returned. 14 - If the "match_type" > 1, then it is assumed to be 1. 15 - If "lookup_array" is a multi-dimensional array, then #N/A is returned. 16 - If "lookup_array" refers to multiple rows or columns, then #N/A is returned. |
© 2022 Better Solutions Limited. All Rights Reserved. © 2022 Better Solutions Limited Top