## 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*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:

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 |

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