## How to Use the MATCH Function and Formula 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 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 functionis a number representing the position of the matchedlookup_valuein thelookup_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:

A | B | C | D | E | |

1 | Item Code | Item Name | Qty | Unit Cost ($) | Unit Price ($) |

2 | Item011 | Dell Desktop PC 11 | 20 | 200 | 215 |

3 | Item012 | Dell Desktop PC 12 | 25 | 150 | 750 |

4 | Item013 | Dell Desktop PC 13 | 26 | 210 | 234 |

5 | Item014 | Dell Desktop PC 14 | 28 | 780 | 795 |

6 | Item015 | Dell Desktop PC 15 | 30 | 1040 | 1060 |

7 | Item016 | Dell Desktop PC 16 | 40 | 230 | 245 |

8 | Item017 | Dell Desktop PC 17 | 41 | 274 | 290 |

#### 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.