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:
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.
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.
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).
