Returns the relative cell position of value.
The Excel MATCH function find the row or column of a specified value (lookup_value) containing in each data set (lookup_array) based on a given type [match_type].
For Example, if cell B3 in a month list contains ‘Feb’, the formula =MATCH(B3,B1:B10,0) returns 2 (3rd row in the months list).
Most often MATCH function is used together with INDEX functions to search value.
=MATCH(lookup_value, lookup_array, [match_type])
lookup_value – Value to find.
lookup_array – Data set containing the lookup_value
match_type [optional] – Number 1,0 and -1 provide three options to match lookup_value:
- Match type option 1, finds the largest value less than or equal to the lookup_value (lookup_array must be sorted in ascending order). Match type 1 is the default option in the formula.
- Match type option 0, find the first exact match of lookup_value (lookup_array need not be sorted).
- Match type option -1, finds the smallest value greater than or equal to the lookup_value (lookup_array must be sorted in descending order).
A number representing the position of lookup_value
- The MATCH function return row or column number which is not useful by itself but can combine with other functions as part of a more complicated formula.
- Match function returns the ROW number within the selected cell range but assigns same row number in case the text is not unique.
- The MATCH function can find and match numbers, logical values, or text strings.
- For text criteria that include logical operators (>,<,<>,=) or wildcards symbols (*,?) for partial matches must be enclosed in double quotation.
To get position of largest value in the range like here to get Max sales of BOB. we apply formula =MATCH(C7,C6:C11) it return 2 as it is highest sales.
Keep Reading Similar Functions:
Keep Reading Similar Blog:
Understand using the Excel INDEX and MATCH functions for locating data and returning its value together with dynamic multiple Drop-Down lists (Advance users).