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, the lookup_array does not have to be sorted 1 (> 0) = Closest match, exact or the next smallest (default) -1 (< 0) = Closest match, exact or the next largest |
REMARKS |
* XMATCH was added in Microsoft 365 to replace this function. * For an illustrated example refer to the page under Lookup Functions * 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. * To include the actual wildcard characters use a tilde prefix (~?), (~*) and (~~). * The "lookup_value" can be a value (number, text, or logical value) or a cell reference. * If "lookup_value" cannot be found and "match_type" = 0, then #N/A is returned. * If "lookup_array" references more than a single column (or row) of cells, then #N/A is returned. * If "lookup_array" references a multi dimensional array, then #N/A is returned. * If "match_type" = 0 then "lookup_array" does not have to be sorted. * If "match_type" = 0 and "lookup_value" does not exist, then #N/A is returned * If "match_type" > 0 then "lookup_array" must be sorted into ascending order (-1, 0, 1, a, A, FALSE, TRUE). * If "match_type" > 0 and "lookup_value" cannot be found, then the next smallest value is returned. * If "match_type" > 0 and "lookup_value" is smaller than the smallest value in the first column, then #N/A is returned. * If "match_type" > 0 and "lookup_value" is larger than the largest value in the first column, then the largest value is returned. * If "match_type" < 0 then "lookup_array" must be sorted into descending order (TRUE, FALSE, A, a, 1, 0, -1). * If "match_type" < 0 and "lookup_value" cannot be found, then the next largest value is returned. * If "match_type" < 0 and "lookup_value" is smaller than the smallest value in the first column, then the largest value is returned. * If "match_type" < 0 and "lookup_value" is larger than the largest value in the first column, then #N/A is returned. * You can use the XLOOKUP function to return a value in the same row (or column). * 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 LOOKUP function to return a value in a row (or column) that matches a value in a column (or row). * You can use the CHOOSE function to return the value in a list based on an index number. * You can use the INDEX and MATCH combination to lookup a value in any column. * You can use the OFFSET and MATCH combination to lookup a value in any column. * You can use the IFNA function to return the value or something else if it evaluates to #N/A. * This function has been available since Excel 1997. * For the Microsoft documentation refer to support.microsoft.com * For the Google documentation refer to support.google.com |
|
1 - What is the position of the value "Wheels" in the range "B1:B3". Closest match. 2 - What is the position of the value "Wheels" in the range "B1:B3". Closest match. True = 1 which is > 0. 3 - What is the position of the value "Bearings" in the range "B1:B3". Closest match. 4 - What is the position of the value "Bearings" in the range "B1:B3". Exact match. False = 0. 5 - What is the position of the value "NoMatch" in the range "B1:B3". Closest match. Column is in ascending order. 6 - What is the position of the value "NoMatch" in the range "B1:B3". Exact match. There is no match. 7 - What is the position of the value "Bear" in the range "B1:B3". Closest match. Column is in ascending order. Smaller than the smallest value. 8 - What is the position of the value "Young" in the range "B5:B7". Closest match. Column is in descending order. Larger than the largest value. |
© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited Top