MATCH

Description: Returns the relative position of an item in an array that matches a specified value and 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 can be an array or an array reference.
Match_type is the number -1, 0, or 1. Match_type specifies how the software 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, 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:

Suppose A2:A5 contain "Bananas", "Oranges", "Apples", "Pears", respectively. B2:B5 contain "25", "38", "40", "41", respectively.

MATCH(39, B2:B5, 1) = 2 [because there is not an exact match, the position of the next lowest value (38) in the range B2:B5 is returned]
MATCH(41, B2:B5, 0) = 4 [the position of 41 in the range B2:B5]
MATCH(40, B2:B5, -1) = #N/A [returns an error because the range B2:B5 is not in descending order]