# MATCH

Returns the relative position of an item in an array (array: Used to build single formulas that produce multiple results or that operate on a group of arguments that are arranged in rows and columns. An array range shares a common formula; an array constant is a group of constants used as an argument.) that matches a specified value in a specified order. 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.

Syntax

MATCH(lookup_value,lookup_array,match_type)

Lookup_value     is the value you use to find the value you want in a table.

• Lookup_value is the value you want to match in lookup_array. 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_value can be a value (number, text, or logical value) or a cell reference to a number, text, or logical value.

Lookup_array     is a contiguous range of cells containing possible lookup values. Lookup_array must be an array or an array reference.

Match_type     is the number -1, 0, or 1. Match_type specifies how Microsoft Excel matches lookup_value with values in lookup_array.

• If match_type is 1, MATCH finds the largest value that is less than or equal to lookup_value. Lookup_array must be placed in ascending order: ...-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE.
• If match_type is 0, MATCH finds the first value that is exactly equal to lookup_value. Lookup_array can be in any order.
• If match_type is -1, MATCH finds the smallest value that is greater than or equal to lookup_value. Lookup_array must be placed in descending order: TRUE, FALSE, Z-A, ...2, 1, 0, -1, -2, ..., and so on.
• If match_type is omitted, it is assumed to be 1.

Remarks

• MATCH returns the position of the matched value within lookup_array, not the value itself. For example, MATCH("b",{"a","b","c"},0) returns 2, 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 match_type is 0 and lookup_value is text, you can use the wildcard characters, question mark (?) and asterisk (*), in lookup_value. 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 (~) before the character.

Example

The example may be easier to understand if you copy it to a blank worksheet.

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

Note   Do not select the row or column headers.

Selecting an example from Help
1. Press CTRL+C.
2. In the worksheet, select cell A1, and press CTRL+V.
3. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.
A B
Product Count
Bananas 25
Oranges 38
Apples 40
Pears 41
Formula Description (Result)
=MATCH(39,B2:B5,1) Because there is not an exact match, the position of the next lowest value (38) in the range B2:B5 is returned. (2)
=MATCH(41,B2:B5,0) The position of 41 in the range B2:B5. (4)
=MATCH(40,B2:B5,-1) Returns an error because the range B2:B5 is not in descending order. (#N/A)

Applies to:
Excel 2003