MATCH function

Returns the relative position of an item in an array 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)

Argument

Description

Remarks

lookup_value

The value you use to find the value you want in a table. The argument 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.

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

lookup_array

A contiguous range of cells containing possible lookup values. The argument lookup_array must be an array or an array reference.

• 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_type

The number -1, 0, or 1. The argument match_type specifies how 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. The argument 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. The argument 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. The argument 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.

General remarks

• 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, lookup_value can contain the wildcard characters asterisk (*) and question mark (?). An asterisk matches any sequence of characters; a question mark matches any single character.

Example

To make the following example easier to understand, you can copy the data to a blank sheet and then enter the function underneath the data. Do not select the row or column headings (1, 2, 3... A, B, C...) when you copy the sample data to a blank sheet.

 1 2 3 4 5
 A B Product Count Banana 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)
 Rate this content: