Excel MATCH Function and Formula with Examples

How to Use the MATCH Function in Excel

In this tutorial, you'll learn how to use the Excel MATCH function. 

MATCH is a function to search for a specified value in a range of cells and return a number that represents the relative position.

MATCH Syntax

The syntax of the MATCH function is as follows:

=MATCH(lookup_value, lookup_array, [match_type])

Return Value

The number representing the position of the matched lookup_value

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. It defaults to 1 if omitted.

Remarks

• lookup_value can be a number, text, logical value, or a cell reference.
• If you set match_type to -1, MATCH() searches for the smallest value that is greater than or equal to lookup_value. 
• If match_type is -1, place the values in lookup_array in descending order.
• If match_type is 0, MATCH() searches for the first value that is equal to lookup_value. 
• The values in lookup_array can be in any order if match_type is 0.
• If you set match_type to 1, MATCH() searches for the largest value that is less than or equal to lookup_value. 
• If match_type is 1, place the values in lookup_array in ascending order.
• MATCH() is not case-sensitive.
• If MATCH() can't find a match, it 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 lookup_value. 

Excel MATCH Function Examples

Demo data:

Searching for an exact match

You can perform an exact match, as the following example shows:

=MATCH(21,C2:C8,0)

MATCH() searches for an item that Qty is equal to 21 in the range C2: C8. It returns #N/A because there is not a match.

Now, let's change lookup_value to 25, and the formula is as follows:

=MATCH(25,C2:C8,0)

MATCH() searches for an item that Qty is equal to 25 in the range and return 2.

Search for the largest value

The following example searches for the largest value that is less than or equal to 27:

=MATCH(27,C2:C8,1)

MATCH() returns 3, which is the position of cell C4.

Search for the smallest value

The following example searches for the smallest value that is greater than or equal to 27:

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

The above formula returns #NA because the values in the range not placed in descending order.

If you place all values in descending order, the above formula returns 4, which is the position of 28 in the range.

Partial Matching

You can use MATCH() to perform a partial matching, as the following example shows:

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

To perform a partial matching, you can use the wildcard characters (?) or (*).

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 ~?.

D*p ?? 13 matches for a text that:

• Begins with D, followed by any sequence of characters
• Then followed by p and space
• Then followed by any two characters and space
• And ends with 13

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

MATCH() is not case-sensitive

Lower case and upper case characters are treated the same in MATCH().

Example:

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

and another example:

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

Both formulas return 2, which is the position of Apple in the array.

Combination of MATCH and INDEX

You can use the combination of MATCH and INDEX to search for a specified value and retrieve its related information when there is a match.

Refer to the following article that describes how to use the combination of MATCH() and INDEX():

How to use INDEX and MATCH

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