Description
The MATCH function searches for a specified range of cells in one item, and then returns the relative position of that element in the range. For example , if the range A1 : A3 contains the values 5, 25 and 38 , the formula= MATCH (25 , A1: A3, 0 )returns the number 2 , because 25 is the second item in the range .Use MATCH instead of one of the LOOKUP functions when you need the position of an item in a range instead of the item itself . For example, you can use the MATCH function to provide a value for the row argument of the INDEX function .syntaxMATCH ( lookup_value ; lookup_array ; match_type ] )The MATCH function syntax has the following arguments :Mandatory lookup_value . Value to search for in lookup_array . For example, when looking for a number in the telephone directory , use the person's name as the lookup value , but the value you want is the phone number.The lookup_value argument can be a value (number, text, or logical value) or a cell reference to a number, text, or logical value.Mandatory lookup_array . Range of cells in which the search is performed .Optional match_type . May be the number -1, 0 or 1. Match_type The argument specifies how Excel matches lookup_value with values lookup_array . The default value for this argument is 1 .The following table describes how the function looks for configuration values as match_type argument.match_type Behavior1 u omitted MATCH finds the largest value that is less than or equal to lookup_value . Lookup_array values must be placed in ascending order , for example : ... -2, -1 , 0, 1 , 2, ... , AZ , FALSE , TRUE .0 MATCH finds the first value that is exactly equal to lookup_value . Lookup_array values can be in any order .-1 MATCH finds the smallest value that is greater than or equal to lookup_value . Lookup_array values must be placed in descending order , for example, TRUE, FALSE , ZA, ... 2, 1, 0 , -1 , -2 , ..., etc. .notesMATCH returns the position of the matching value within lookup_array , not the value itself. For example , MATCH ("b ", { "a" , "b ", " c "} , 0) returns 2, the relative position of "b" within the array { "a" , "b ", " c "} .MATCH is not case sensitive when searching text values.MATCH If you can not find a match, it returns the error value # N / A.If match_type is 0 and lookup_value is a text string , you can use wildcards: the question mark and the asterisk ( *), in lookup_value (? ) . A question mark matches any single character ; an asterisk matches any sequence of characters. To find a question mark or an actual asterisk, type a tilde ( ~ ) before the character.
Example
The example may be easier to understand if you copy it to a blank worksheet .A B C1 Proceeds Count2 Bananas 253 Oranges 384 Apples 40Pears May 416 Formula Description Result
7 = MATCH ( 39 , B2: B5 , 1) Since there is no exact match, the position of the next lower value ( 38 ) is returned within the range B2 : B5. 28 = MATCH ( 41 , B2: B5 , 0) position of the value 41 in the range B2 : B5. 49 = MATCH ( 40 , B2: B5 , -1 ) returns an error because the values in the range B2 : B5 are not in descending order.
