Excel MATCH function (15 Examples with INDEX and simple)

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.

In this tutorial: hide

1. Syntax of the MATCH function
2. An example of MATCH in EXCEL
3. Using the cell reference in MATCH function
4. An example of searching in rows (horizontally) by MATCH function
5. Exploring the Match_type argument
6. Data contains duplicate values with 0 value
7. Using the 1 value for Match_Type example
8. Using the -1 for Match_Type
9. Using INDEX with MATCH function

Syntax of the MATCH function

MATCH(lookup_value, lookup_array, [match_type])

In the syntax:

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

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 the next example horizontally.

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

=MATCH("Maze",B2:B11,0)

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

MATCH is a case-insensitive function. That means Banana and banana have the same meaning.

Using the cell reference in MATCH function

It is not generally a good practice to use constant literals in formulas as used in the 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.

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 to the range of the look_up array for the same row e.g. A2:E2. If you refer to something like this: A2:E5 then an incorrect result may be returned by the 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:

It returned position = 3.

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

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:

Value	Description
1	This is the default. If you omit this argument, the 1 is taken. It specifies finding 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 a 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:

You can see that 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 to numeric format and placed it in ascending order. See the formula and output for different entries:

The MATCH formula in D13:

=MATCH(D13,A2:A11,1)

The results:

Entered 9:

Entered 11:

You can see that 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)

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 the above example and used the -1 match_type:

The Formula:

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

The output for 7:

It produced a #N/A error when I entered 7. Though 7 exists, however, lookup_array is not sorted in descending order with -1 which 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:

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

Output as 7 is entered:

For value 19 that does not exist:

Using INDEX with MATCH function

The INDEX function is used to return the value from the given array, range, or table. The syntax of the 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 the 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 the above examples. This time, I will enter the Product Name in the D13 cell, and the INDEX/MATCH combination will return the price from the C column for the entered product.

The Formula of INDEX MATCH:

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

The result for Dinner Set:

For Rice product name:

As 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 the same formula. See the results: