Rational
Returns the relative cell position of value.
Description
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.
Syntax
=MATCH(lookup_value, lookup_array, [match_type])
Parameters
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).
Return Value
A number representing the position of lookup_value
Key Notes
- 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:
VLOOKUP
The VLOOKUP function extract data in a vertical pattern which means heading in 1st column and value begins from the 2nd column and retrieve data from left to right.
ADDRESS
The ADDRESS function returns cell address from given row and column numbers of a worksheet. For example, if you pass ADDRESS(5,6) the function returns $F$5.
MATCH
The MATCH function finds row or column of a given value containing in a data set, cell B3 in a list contains ‘Feb’ =MATCH(B3,B1:B10,0) returns 2 (3rd row).
HLOOKUP
The HLOOKUP function extract data in a horizontal pattern which means heading in 1st row and value begins from 2nd row, and retrieve data from left to right.
CHOOSE
The CHOOSE function returns specific information from a list of data points, =CHOOSE(4,”Railways”,”Roads”,”Airways”,”Waterways”) returns Waterways.
Keep Reading Similar Blog:
Excel INDEX & MATCH function (Multiple Dependent Drop-Down lists)
Understand using the Excel INDEX and MATCH functions for locating data and returning its value together with dynamic multiple Drop-Down lists (Advance users).