What does this function do?
Searches for a value within an array and returns the relative position of the value in that array.
Syntax:
MATCH(lookup_value, lookup_array, [match_type])
- 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.
Match_type
|
Behavior
|
1 or omitted
|
MATCH finds the largest value that is less than or equal to lookup_value. The values in thelookup_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_arrayargument can be in any order.
|
-1
|
MATCH finds the smallest value that is greater than or equal to lookup_value. The values in thelookup_array argument must be placed in descending order, for example: TRUE, FALSE, Z-A, ...2, 1, 0, -1, -2, ..., and so on.
|
Examples:
Let's say we have a range like the following (A1:B5):
| A | B | |
|---|---|---|
| Product | Count | |
| A | 25 | |
| B | 38 | |
| C | 40 | |
| D | 41 |
=MATCH(39,B2:B5,1) = 2, because there is not an exact match, the position of the next lowest value (38) in the range B2:B5 is returned.
=MATCH(41,B2:B5,0)=4, the position of the value 41 in the range B2:B5.
=MATCH(40,B2:B5,-1) = #N/A, returns an error because the values in the range B2:B5 are not in descending order.
No comments:
Post a Comment