The MATCH function in Excel

The Excel MATCH function returns the relative position of the specified item from the given range of cells.

MATCH cell reference

For example, we have fruit names in column B:

  • Apple
  • Mango
  • Banana
  • Strawberries
  • Orange

We want to search the position of Banana; this is how the MATCH function can be used for that:

=MATCH(“Banana”,B1:B5,0)

The return result will be 3. On the basis of this formula, let us explore the arguments of the MATCH function after its general syntax:

MATCH(lookup_value, lookup_array, [match_type])

Tip: You should use MATCH function instead of VLOOKUP or HLOOKUP if the position of the item is required. You may also use MATCH with INDEX function as shown in the last section.

Where:

  • The lookup_value is the term/item you are looking for its position. In our example, the “Banana” is the lookup_value. You may use text, numbers, a cell reference there as well. This is required argument.
  • In the above example formula, the B1:B5 is the lookup_array. It defines the range of cells or array reference where you want to search the given lookup_value. This is also required argument.
  • The match_type is an optional argument. You may use 1, 0 and -1 value for this argument. It needs further details, so I will explain with examples below. The default value is 1.
  • MATCH is case insensitive function. That means Banana and banana has the same meaning.

Important Note: The range of cells should be from the same column or same row for the correct results. Also, you may use the MATCH function vertically or horizontally arranged recordsets.

An example of MATCH in EXCEL

The first example uses the Excel sheet that stores Product information like Product ID, Product Name, Price, and Status. We will find out the relative position of a product; first by vertical lookup and in next example horizontally.

The formula used for the MATCH function to search “Maze”:

=MATCH(“Maze”,B2:B11,0)

Excel MATCH

You see, in the given range (B2:B11), the position of Maze is 4.

Using the cell reference in MATCH function

It is not generally the good practice using constant literals in formulas as used in above example for the demo only. For the next example, I will refer a cell (D13) in the MATCH formula. Just enter the product name in D13, press enter and D14 will be updated by the product position in the given look_up array.

MATCH cell reference

The following formula is used in D14 cell:

=MATCH(D13,B2:B11,0)

An example of searching in rows (horizontally) by MATCH function

You may also use the MATCH function for searching the product position of horizontally managed recordsets.

For that, refer the range of look_up array for the same row e.g. A2:E2. If you refer something like this: A2:E5 then incorrect result may be returned by Excel Match function.

For demonstrating that, I will search the position of “Plates” in A9 to D9:

The formula:

=MATCH(D13,A9:D9,0)

As I entered the Product price = 7, see the result:

MATCH horizontal

It returned position = 3.

Exploring the Match_type argument

The third argument in the MATCH function is Match_type. Until now we used zero value for all the examples in above section. Following are the details of three possible values:

  • 1 – This is the default. If you omit this argument, the 1 is taken. It specifies to find the largest value that is less than or equal to the lookup_value. If you use this option, ensure that the values in lookup_array are in ascending order.
  • 0– The 0 value means to find the exact match while lookup_array values are in any order.It finds the first matched position. So, if your lookup_array has duplicate values, the first matched position is returned.
  • -1 – This value finds the smallest value which is greater than or equal to the lookup_value. In that case, the lookup_array values must be placed in descending order.

Now let us have a look at a few examples in the Match_Type argument.

You can do wildcard search by using ‘*’ and ‘?’ wildcards if Match_Type is ‘0’ and look_up value is text.

Data contains duplicate values with 0 value

In our example sheet, the product name “Rice” is entered twice. See the output as we search the Rice position by MATCH function with 0 value for Match_type:

The formula in D14 cell:

=MATCH(D13,B2:B11,0)

The result:

MATCH duplicate

You can see, it returned the first matched position i.e. 2 rather than 7 (the second occurrence).

Using the 1 value for Match_Type example

For that, I have changed the Product ID column value in numeric format and placed in ascending order. See the formula and output for different entries:

The MATCH formula in D13:

=MATCH(D13,A2:A11,1)

The results:

MATCH match_type

Entered 9:

MATCH match_type-2

Entered 11:

MATCH match_type 3

You can see, even though the value 9 does not exist, it returned the closest match. If we use 0 value for Match_Type, the output is:

Formula:

=MATCH(D13,A2:A11,0)

MATCH match_type-4

A #N/A error occurred.

Using the  -1 for Match_Type

First, have a look at the output if we used the same dataset as in above example and using the -1 match_type:

The Formula:

=MATCH(D13,A2:A11,-1)

The output for 7:

MATCH match_type- 1

It produced #N/A error even I entered 7. Though, 7 exist but lookup_array is not sorted in descending order with -1 that resulted in an error.

Now have a look at the output as I re-order the Product ID column in descending order and enter 7 and other values:

The formula:

=MATCH(D13,A2:A11,-1)

Output as 7 is entered:

MATCH match-1

For value 19 that does not exist:

MATCH match-2

Using INDEX with MATCH function

The INDEX function is used to return the value from the given array, range or table. The syntax of INDEX function is:

INDEX(array, row_num, [column_num])

You may provide the row_num or column_num value by using the MATCH function and INDEX function will return its value.

See the following example for learning how to use the INDEX and MATCH functions together. For that, I used the same spreadsheet as in above examples. This time, I will enter the Product Name in D13 cell and INDEX/MATCH combination will return the price from C column for the entered product.

Have a look at formula and output for different products:

The Formula of INDEX MATCH:

=INDEX($C$2:$C$11,MATCH(D13,B2:B11,0))

The result for Dinner Set:

INDEX MATCH

For Rice product name:

INDEX MATCH 2

And as I mentioned earlier, you may use the wildcard characters (* and ?) if Match_type in MATCH is 0 and lookup_value is text. This perfectly suits the above table and same formula. See the results:

INDEX MATCH 3

If you look at the complete graphic of table data then it returned the Sugar price.

Similarly, using the ‘?’ mark wildcard:

INDEX MATCH 4

It returned the Maze price.