# MATCH function

This article describes the formula syntax and usage of the **MATCH** function (function: A prewritten formula that takes a value or values, performs an operation, and returns a value or values. Use functions to simplify and shorten formulas on a worksheet, especially those that perform lengthy or complex calculations.) in Microsoft Excel.

## Description

The **MATCH** function searches for a specified item in a range (range: Two or more cells on a sheet. The cells in a range can be adjacent or nonadjacent.) of cells, and then returns the relative position of that item in the range. For example, if the range A1:A3 contains the values 5, 25, and 38, then the formula

**=****MATCH(****25,A1:A3,0)**

returns the number 2, because 25 is the second item in the range.

Use **MATCH** instead of one of the **LOOKUP** functions when you need the position of an item in a range instead of the item itself. For example, you might use the **MATCH** function to provide a value for the ** row_num** argument of the

**INDEX**function.

## Syntax

MATCH(lookup_value, lookup_array, [match_type])

The MATCH function syntax has the following arguments (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.):

**lookup_value**Required. The value that you want to match in. For example, when you look up someone's number in a telephone book, you are using the person's name as the lookup value, but the telephone number is the value you want.*lookup_array*

The ** lookup_value** argument can be a value (number, text, or logical value) or a cell reference to a number, text, or logical value.

**lookup_array**Required. The range of cells being searched.**match_type**Optional. The number -1, 0, or 1. Theargument specifies how Excel matches*match_type*with values in*lookup_value*. The default value for this argument is 1.*lookup_array*

The following table describes how the function finds values based on the setting of the ** match_type** argument.

Match_type |
Behavior |
---|---|

1 or omitted | MATCH finds the largest value that is less than or equal to . The values in the lookup_value argument must be placed in ascending order, for example: ...-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE.lookup_array |

0 | MATCH finds the first value that is exactly equal to . The values in the lookup_value argument can be in any order.lookup_array |

-1 | MATCH finds the smallest value that is greater than or equal to. The values in the lookup_value argument must be placed in descending order, for example: TRUE, FALSE, Z-A, ...2, 1, 0, -1, -2, ..., and so on.lookup_array |

** Notes **

**MATCH**returns the position of the matched value within, not the value itself. For example,*lookup_array***MATCH(****"b",{"a","b","c"},0)**returns 2, which is the relative position of "b" within the array {"a","b","c"}.**MATCH**does not distinguish between uppercase and lowercase letters when matching text values.- If
**MATCH**is unsuccessful in finding a match, it returns the #N/A error value. - If
is 0 and*match_type*is a text string, you can use the wildcard characters — the question mark (*lookup_value***?**) and asterisk (*****) — in theargument. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (*lookup_value***~**) before the character.

## Example

Use the embedded workbook shown here to work with examples of this function. You can inspect and change existing formulas, enter your own formulas, and read further information about how the function works.

To work in-depth with this workbook, you can download it to your computer and open it in Excel. For more information, see the article Download an embedded workbook from OneDrive and open it on your computer.