MATCH function
Summary
Looks up values in a reference or array
Syntax
MATCH(lookup_value, lookup_array, [match_type])
The MATCH function syntax has the following arguments:
• lookup_value Required. The value that you want to match in lookup_array. For example, when you look up someone's number in a telephone book, you are using the person's name as the lookup value, but the telephone number is the value you want. The lookup_value argument can be a value (number, text, or logical value) or a cell reference to a number, text, or logical value.
• lookup_array Required. The range of cells being searched.
• match_type Optional. The number -1, 0, or 1. The match_type argument specifies how Excel matches lookup_value with values in lookup_array. The default value for this argument is 1. The following table describes how the function finds values based on the setting of the match_type argument.
Match_type | Behavior
1 or omitted | MATCH finds the largest value that is less than or equal to lookup_value. The values in the lookup_array argument must be placed in ascending order, for example: ...-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE.
0 | MATCH finds the first value that is exactly equal to lookup_value. The values in the lookup_array argument can be in any order.
-1 | MATCH finds the smallest value that is greater than or equal tolookup_value. The values in the lookup_array argument must be placed in descending order, for example: TRUE, FALSE, Z-A, ...2, 1, 0, -1, -2, ..., and so on.
• MATCH returns the position of the matched value within lookup_array, not the value itself. For example, MATCH("b",{"a","b","c"},0) returns 2, which is the relative position of "b" within the array {"a","b","c"}.
• MATCH does not distinguish between uppercase and lowercase letters when matching text values.
• If MATCH is unsuccessful in finding a match, it returns the #N/A error value.
• If match_type is 0 and lookup_value is a text string, you can use the wildcard characters — the question mark (?) and asterisk (*) — in the lookup_value argument. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) before the character.
• lookup_value Required. The value that you want to match in lookup_array. For example, when you look up someone's number in a telephone book, you are using the person's name as the lookup value, but the telephone number is the value you want. The lookup_value argument can be a value (number, text, or logical value) or a cell reference to a number, text, or logical value.
• lookup_array Required. The range of cells being searched.
• match_type Optional. The number -1, 0, or 1. The match_type argument specifies how Excel matches lookup_value with values in lookup_array. The default value for this argument is 1. The following table describes how the function finds values based on the setting of the match_type argument.
Match_type | Behavior
1 or omitted | MATCH finds the largest value that is less than or equal to lookup_value. The values in the lookup_array argument must be placed in ascending order, for example: ...-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE.
0 | MATCH finds the first value that is exactly equal to lookup_value. The values in the lookup_array argument can be in any order.
-1 | MATCH finds the smallest value that is greater than or equal tolookup_value. The values in the lookup_array argument must be placed in descending order, for example: TRUE, FALSE, Z-A, ...2, 1, 0, -1, -2, ..., and so on.
• MATCH returns the position of the matched value within lookup_array, not the value itself. For example, MATCH("b",{"a","b","c"},0) returns 2, which is the relative position of "b" within the array {"a","b","c"}.
• MATCH does not distinguish between uppercase and lowercase letters when matching text values.
• If MATCH is unsuccessful in finding a match, it returns the #N/A error value.
• If match_type is 0 and lookup_value is a text string, you can use the wildcard characters — the question mark (?) and asterisk (*) — in the lookup_value argument. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) before the character.
Example
=MATCH(39,B2:B5,1)
=MATCH(41,B2:B5,0)
=MATCH(40,B2:B5,-1)
=MATCH(41,B2:B5,0)
=MATCH(40,B2:B5,-1)