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. 5^th 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 4^th 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. 1^st 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,

Don’t forget to leave your valuable comments!

If you liked this article and want to learn more similar tricks, please Subscribe us or follow us on Social Media by clicking below buttons: