The MATCH Excel Function

 

The MATCH function looks up a text or numerical value in a specified range and then returns the relative position of that item in the range.   The result of the MATCH formula is an index number.  This article explains the MATCH function with some examples.

The following Youtube video goes through the practicalities of the lessons below. The file for the video can be found below.

Match Formula.xlsx

 
 

The MATCH function returns either the row or column number of the item being looked up.  The MATCH function is often used in conjunction with the INDEX formula as the INDEX formula needs row and column numbers to return a result so the two work perfectly together.


The syntax for the match function is as follows:

 

MATCH(lookup value, lookup range, match type)

 

  • The lookup value is the value which is being looked up in the lookup range.

  • The lookup range is where the lookup value is held.

  • The match type is a number from -1, 0 and 1. The match type identifies how Excel matches the lookup value with values in lookup range. If left blank the default value is 1.



The Match Type


The Match type is the part which is most important to understand.  Mostly people will be searching for an exact match. 

  • When the match type is 1, the MATCH formula returns the largest value in the range that is less than or equal to lookup value. The lookup range needs to be sorted in ascending order for the match type to work correctly.

  • When the match type is 0, the MATCH formula returns the first value exactly equal to lookup value. lookup range does not need to be sorted.

  • When the match type is -1, the MATCH formula returns the smallest value that is greater than or equal to lookup value. The lookup range must be sorted in descending order for the match type to work correctly.

If the match type is left out then it is assumed to be 1.


Let’s have a look at the three match types using a single number (65)

MATCH type

We use number 65 in the above example – the first table is sorted in Ascending order, the second table is unsorted and the final table is sorted in descending order.

Match Type  1 =MATCH(A8,B2:B7,1)   Produces 3 as 62 is less than 72 but greater than 62. 

Match Type  0 = MATCH(D8,E2:E7,0)  Produces an error as it is searching for an exact match.

Match Type  -1 = MATCH(G8,H2:H7,-1)   Produces 2 as 72 is greater than 65 and 62 is less than 65.

The following Excel file has the above example.