MATCH

Returns the relative cell position of value
Returns the relative position of an item in an array

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.
Example

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.

 
For detail analysis please read MATCH and INDEX blog

Keep Reading Similar Functions:

Returns the relative cell position of value

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

Read More »
Lookup a value in a left column of a table or an array in Excel

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.

Read More »
Lookup a value in the top row of a table or an array in Excel

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.

Read More »
Return address of a cell in Excel

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.

Read More »
Returns a specific position from a list in Excel

CHOOSE

The CHOOSE function returns specific information from a list of data points, =CHOOSE(4,”Railways”,”Roads”,”Airways”,”Waterways”) returns Waterways.

Read More »

Keep Reading Similar Blog: