Click here to download the Excel Workbook which contains below example
Match Function: Excel Match function will help to
Below Quick snapshot of Excel Match Formula
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).
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.
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).
-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)
Question 1: Get the Row number for Excel in the list
Formula in Cell H10=MATCH(G10,$E$9:$E$12,0)
Result 3
Question 2: Get the Row number for Excel in the list
Formula in Cell F25=MATCH(E25,$E$22:$H$22,0)
Result 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
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