Last modified date

Click here to download the Excel Workbook which contains below example

Match Function: Excel Match function will help to lookup a particular value/text within lookup_array. In result, it will give the position number of lookup value/text value in the selected array. Position can be Column or Row.

Below Quick snapshot of Excel Match Formula

Excel Match Function
Excel Match Function

Syntax of Excel Match Formula

MATCH(lookup_value,lookup_array,[match_type])

  • First Argument: lookup_value this is a compulsory argument
  • Second Argument: lookup_array this is a compulsory argument
  • Third Argument:[match_type] this is a optional argument

Explanation of Arguments in Excel Match Formula

lookup_value: This is a compulsory argument, here we need to select the lookup value for which we want the match and get the position number.

lookup_array: This is a compulsory argument, here we need to select the range to check and match value we have updated in the first argument. The counting of Row and Columns numbers will be started as per the look_up array.

Examples of Match_Type (3rd Argument)

Match_type: This is an optional argument, you can leave it blank. If you leave blank the default value for this argument is 1 (Less than).

Excel match Function
Excel Match_Type 0

0 Exact Match: Finds the Exact value which is equal to lookup_value. It is advisable to use Exact match_type if you are not sure to use which option.

Excel match Function
Excel Match_Type 1

1 Less than: Finds the largest value that is less than or equal to lookup_value. Lookup_array must be in ascending order(0 to 9).

Excel match Function
Excel Match_Type -1

-1 Greater than: Finds the smallest value that is greater than or equal to lookup_value. Lookup_array must be in descending order(9 to 0)

Examples of Excel Match Formula

All Examples are as per match_type=0 (Exact Match)

How to use Excel Match Function in excel
Excel Match Function Example 1

Question 1: Get the Row number for Excel in the list

Formula in Cell H10=MATCH(G10,$E$9:$E$12,0)

Result 3

How to use Excel Match Function
Excel Match Function Example 2

Question 2: Get the Row number for Excel in the list

Formula in Cell F25=MATCH(E25,$E$22:$H$22,0)

Result 3

How to use Excel Match Function
Excel Match Function Example 3

Question 3: Get the Row number for Excel in the list

Formula in Cell H37=MATCH(G37,$E$36:$E$39,0)

Result 4

Excel INDEX MATCH Formulas
Excel INDEX MATCH example

Usually Match function is used along with Index function

Question 4: Get Maths subject Marks for James and Jack

Formula F59=INDEX($E$50:$G$54,MATCH(F$58,$E$50:$E$54,0),MATCH($E59,$E$50:$G$50,0))

Result is 85

Leave a Reply

Your email address will not be published. Required fields are marked *

Post comment