MATCH

Important notice for users of Office 2003    To continue receiving security updates for Office, make sure you're running Office 2003 Service Pack 3 (SP3). The support for Office 2003 ends April 8, 2014. If you’re running Office 2003 after support ends, to receive all important security updates for Office, you need to upgrade to a later version such as Office 365 or Office 2013. For more information, see Support is ending for Office 2003.

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.

ShowHow to copy an example

  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

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.
 
1
2
3
4
5
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