# EXCEL FUNCTION – MATCH

## How to use “MATCH” function in Excel

MATCH function performs lookup for a value in a range and returns its position sequence number as output.

MATCH Function has two required argument i.e. lookup_value, lookup_array and one optional argument i.e. [match_type].

##### Syntax:
`=MATCH(lookup_value,lookup_array,[match_type])`
##### Syntax Description:

lookup_value argument is the used to give text/value/cell reference that you want to match

lookup_array argument is used to give the range in which lookup_value to be matched

[match_type] is optional argument. 1,0,-1 values denote that how match function should behave.

1 or Omitted = Less than [match_type] : Match finds the position of value which is “Equals to or Less than” the lookup value. lookup_array should be in Ascending order is prerequisites of this [match_type]

0 = Exact match [match_type]: Match finds the position of value which is “Exact” the lookup value. Data sorting is NOT required for this [match_type]

-1 = Greater than [match_type]: Match finds the position of value which is “Equals to or Greater than” the lookup value. lookup_array should be in Descending order is prerequisites of this [match_type]

##### Example 1: MATCH function with 1 or Omitted [match_type] (i.e. Less than)

Here, we have sample database and want to match the values with Exact or Less than lookup_value:

• “Column A” has series of values,
• “Column B” shows the lookup_value argument value, that we want to match,
• “Column C” shows the sample formula applied,
• “Column D” shows the output of the function and,
• Explanation is provided in “Columns E”

lookup_value is 9 and Less Than [match_type] (i.e. 1) is applied.

Function will lookup the position of values for either 9 (i.e. Exact Match) or Less than 9. Data series did not contain 9 that is why position of 8 (i.e. Less than 9) is returned in output i.e. 5th position.

##### Example 2: MATCH function with 0 [match_type] (i.e. Exact match):

Here, we have sample database and want to match the values with Exact lookup_value:

• “Column A” has series of values,
• “Column B” shows the lookup_value argument value, that we want to match,
• “Column C” shows the sample formula applied,
• “Column D” shows the output of the function and,
• Explanation is provided in “Columns E”

lookup_value is 9 and Exact [match_type] (i.e. 0) is applied.

Function will lookup the position of values for 9 (i.e. Exact Match). Data series contains 9 at 4th position and output is returned accordingly.

##### Example 3: MATCH function with -1 [match_type] (i.e. Greater than)

Here, we have sample database and want to match the values with Exact or Greater than lookup_value:

• “Column A” has series of values,
• “Column B” shows the lookup_value argument value, that we want to match,
• “Column C” shows the sample formula applied,
• “Column D” shows the output of the function and,
• Explanation is provided in “Columns E”

lookup_value is 9 and Greater Than [match_type] (i.e. -1) is applied.

Function will lookup the position of values for either 9 (i.e. Exact Match) or Greater than 9. Data series did not contain 9 that is why position of 10 (i.e. Greater than 9) is returned in output i.e. 1st position.

##### Things to Remember:

– Function is NOT case sensitive, means it will match “A” for text contains “A” and “a”

– If no match is found in lookup_array then function output will return “#N/A” error

– Ascending order means, A-Z, a-z, -2,-1,0,1,2.. , False-True

– Descending order means, Z-A, z-a, 2,1,0,-1,-2.. , True-False

Hope you learnt this Function,