Excel MATCH Function

Use:
Lookup_value is the value you use to find the value you want in a table. 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)
bulletLookup_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. bulletLookup_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 Microsoft Excel matches lookup_value with values in lookup_array. bulletIf 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. bulletIf match_type is 0, MATCH finds the first value that is exactly equal to lookup_value. Lookup_array can be in any order. bulletIf 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. bulletIf match_type is omitted, it is assumed to be 1.

Remarks bulletMATCH 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"}. bulletMATCH does not distinguish between uppercase and lowercase letters when matching text values. bulletIf MATCH is unsuccessful in finding a match, it returns the #N/A error value. bulletIf 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.

Examples:

match1.jpg (25177 bytes)

Cell C2 formula:   MATCH(75, B2:B6, 0) results in 3 or the value "75" is in the third row of the range B2:B6

match2.jpg (22976 bytes)

Cell C2 formula:   MATCH(50, B1:G1, 0) results in 5 or the value "50" is in the fifth column of the range B1:G1

Related Function:
HLOOKUP and VLOOKUP