Excel MATCH Function and Formula with Examples


How to Use the MATCH Function and Formula in Excel

In this tutorial, you'll learn how to use the Excel MATCH functionMATCH is a function to search for a specified value in a range of cells, and then returns a number representing the relative position.

MATCH Syntax

The syntax of the MATCH function is as follows:

=MATCH(lookup_value, lookup_array, [match_type])

Return Value

The return value from the MATCH function is a number representing the position of the matched lookup_value in the lookup_array.

Arguments

  • lookup_value: The value that you want to match in lookup_array
  • lookup_array: The range of cells or an array reference
  • match_type: The match_type argument is optional. It can be -1, 0, or 1. If match_type omitted, the default value of this argument is 1

Remarks

  • Lookup_value can be a number, text, logical value, or a cell reference.
  • If match_type is -1, MATCH searches for the smallest value that is greater than or equal to lookup_value. Values, in the lookup_array, must be placed in descending order.
  • If match_type is 0, MATCH searches for the first value that is equal to lookup_value. Values, in the lookup_array, can be placed in any order.
  • If match_type is 1, MATCH searches for the largest value that is less than or equal lookup_value. Values, in the lookup_array, must be placed in ascending order.
  • MATCH is not case-sensitive.
  • If lookup_value not found, MATCH returns an error value (#N/A ).
  • If match_type is 0 and lookup_value is text, you can use the wildcard characters the question mark (?) and asterisk (*) in the lookup_value. A question mark matches any single character, and an asterisk matches any sequence of characters. If you want to search for a ? or * use a tilde (~) before the character. For example, ~* and ~?.

Excel MATCH Function Examples

The following is a sample list of items we're going to use in the examples:

 ABCDE
1Item CodeItem NameQtyUnit Cost ($)Unit Price ($)
2Item011Dell Desktop PC 1120200215
3Item012Dell Desktop PC 1225150750
4Item013Dell Desktop PC 1326210234
5Item014 Dell Desktop PC 1428780795
6Item015 Dell Desktop PC 153010401060
7Item016 Dell Desktop PC 1640230245
8Item017Dell Desktop PC 1741274290

Search for an Exact Match

In this example, I show how to search for an exact match. Let's take a look at an example:

=MATCH(21,C2:C8,0)

Here, as you can see, there are three arguments, 21, C2:C8, and 0. The first argument is a lookup value, the second is a lookup array, and the third tells MATCH to search for an exact match.

MATCH searches for an item that has 21 quantities in the range C2:C8, but returns #N/A, because there is no such an item in the list.

Now, let's change the first argument to 25, and the MATCH formula will look like this:

=MATCH(25,C2:C8,0)

MATCH searches for an item that has 25 of quantities in the range C2:C8 and found it in the list. MATCH returns 2, which is the position of 25 in the lookup array {20, 25, 26, 28, 30, 40, 41}.

Search for the Largest Value

In this example, I show you how to use MATCH to search for the largest value that is less than or equal to the lookup value.

The following example illustrates how to search for the largest value in the range:

=MATCH(27,C2:C8,1)

Here, match_type is 1, so MATCH searches for the largest value that is less than or equal to 27 in cells C2 through C8. The result of the above formula is 3, the position of cell C4.

If you look at the list above, the lookup array, the reference to a range of cells C2 through C8, doesn't contain 27. Since match_type is 1, MATCH searches for the largest value that less than or equal to 27, and 26 is the matched value. 26 is the value of cell C4 located in the 3rd position in the range C2:C8.

Note that if match_type is 1, the values, in the range, must be placed in ascending order. Otherwise, MATCH returns the error value #NA.

Search for the Smallest Value

In this example, I'm going to show you an example of using the MATCH function to search for the smallest value that is less than or equal to the lookup value.

Let's take the previous formula and change match_type from 1 to -1 and see what is different.

=MATCH(27,C2:C8,-1)

In this formula, MATCH searches for the smallest value that is greater than or equal to 27, and returns #NA. Because the values in the range C2:C8 were not placed in descending order.

In the above formula, MATCH searches for the smallest value that is greater than or equal to 27 and returns #NA. Because the values in the range C2: C8 not placed in descending order.

Now let's say you sort the above list to get all the values in the range C2:C2 placed in descending order as follows:

(41, 40, 30, 28, 26, 25, 20)

And then you run the above formula. The result is 4, which is the position of 28 in the range C2:C8.

As you can see, four numbers are greater than the lookup value (27), and they are 41, 40, 30, and 28. 28 is the matched value because it is the smallest one among them. And 28 is the value of the cell C5 located in the 4th position in the range C2:C8.

Partial Matching

You can perform a partial matching using the MATCH function. To perform a partial matching, you need to use the wildcard characters, ? or *.
Let's say you want to search for an item name in the above list.
The following is an example of using MATCH to search for an item name:

=MATCH("D*p ?? 13",B2:B8,0)

Here, both wildcard characters (*) and (?) used in the lookup value. The wildcard asterisk (*) matches any sequence of characters, and question mark (?) matches any single character, so the lookup value D*p ?? 13 matches for an item name that begins with D, and then any sequence of characters, and then p, and then a single space, and then any two characters, and then a single space, and then 13.

MATCH returns 3, which is the position of Dell Desktop PC 13 in the range B2:B8.

MATCH is Not Case-Sensitive

MATCH is not case-sensitive, so lower case and upper case characters treated the same.

Now let's take a look at the following two examples:

=MATCH("apple",{"Orange","Apple","Mango","Grape"},0)

and

=MATCH("applE",{"Orange","Apple","Mango","Grape"},0)

These two formulas search for the same word, and both return 2, which is the position of Apple in the lookup array.

Using MATCH with INDEX

You can use the MATCH and INDEX functions together. Read and learn how to use MATCH and INDEX to look up for a specified value in a range of cells.

In this tutorial, you've learned how to use the MATCH function in Excel. MATCH is a function used to search for a specified value in a range of cells, and then returns a number representing the relative position.

Leave a Reply

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