Buzz
The Match function is a widely used tool in Excel for data computation and manipulation. It helps you find a specific value within a range, then returns its position within that range.
Explore the Syntax and Usage of Match Function in Excel
Description
Unlocking the Potential of the MATCH Function
Syntax
=MATCH(lookup_value,lookup_array,match_type)
Explaining Parameters
- lookup_value: The value you want to find in the array (lookup_array), which can be a number, text, logical value, or a reference to a cell containing a number, text, or logical value.
- lookup_array: The array, range, or cell range to be searched.
- match_type: The type of match: -1, 0, or 1.
1 or omit (Less than): The MATCH function searches for the largest value less than or equal to the lookup_value. If you choose this match type, the lookup_array must be sorted in ascending order.
0 (Exact match): MATCH function searches for the first value exactly equal to the lookup_value, and values in the lookup_array can be sorted in any order.
-1 (Greater than): MATCH function searches for the smallest value greater than or equal to the lookup_value. Values in the lookup_array must be sorted in descending order.
Note
- MATCH function returns the position of the lookup value in the lookup_array, not the lookup value itself.
- Case sensitivity is not considered when searching for text values.
- If the lookup value is not found in the lookup_array, the MATCH function returns an error value.
- If match_type is 0 and the lookup_value is text, it can contain wildcard characters * (matches any sequence of characters) and ? (matches any single character). To search for a literal question mark or asterisk, precede the character with a tilde ~.
Example
Given the data table:
When the match type is 1 or omitted, for example, searching for the position of 64 in the column Total:
=MATCH(64,C6:C9,1)
Since the value 64 is not present in the Total column, the function will return the position of the largest value less than 64 (63), resulting in:
When the match type is 0, for example, finding the position of 65 in the Total column:
=MATCH(65,C6:C9,0)
When the match type is -1, for example: =MATCH(65,C6:C9,-1) and it results in an error because the array is not sorted in descending order.
Utilizing the Match function is fairly straightforward. For various processing and computation requirements, you can combine the Match function with other functions in Excel to achieve highly efficient data processing. Wishing you success!
