MATCH

See also A-Z Spreadsheet Function List

Description A specified value is compared against values in a range. The position of the matching value in the search range is returned.

Syntax MATCH(lookup_value, lookup_range, comparison)

lookup_value is the value against which to compare. It can be a number, text, or logical value or a reference to a cell that contains one of those values.

lookup_range is the range to search and contains only one row or one column. The range can contain numbers, text, or logical values.

comparison is a number that represents the type of comparison to be made between lookup_value and the values in lookup_range. When you omit this argument, comparison method 1 is assumed.

When comparison is 1, the largest value that is less than or equal to lookup_value is matched. When using this comparison method, the values in lookup_range must be in ascending order (e.g., ...-2, -1, 0, 1, 2..., A through Z, False, True).

When comparison is 0, the first value that is equal to lookup_value is matched. When using this comparison method, the values in lookup_range can be in any order.

When comparison is -1, the smallest value that is greater than or equal to lookup_value is matched. When using this comparison method, the values in lookup_range must be in descending order (e.g., True, False, Z through A, ...2, 1, 0, -1, -2...).

Remarks When using comparison method 0 and lookup_value is text, lookup_value can contain wildcard characters. The wildcard characters are * (asterisk), which matches any sequence of characters, and ? (question mark), which matches any single character.

         When no match is found for lookup_value, #N/A is returned.

See Also HLOOKUP , INDEX , LOOKUP , and VLOOKUP functions

Examples In the preceding spreadsheet:

         MATCH(7600, B2:B7,1) returns 5

         MATCH("D*", A2:A7,0) returns 2