MATCH

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 )

Parameter	Description
lookup_value	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	The range to search; it contains only one row or one column. The range can contain numbers, text, or logical values.
Comparison	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 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 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 (for example, ...-2, -1, 0, 1, 2..., A through Z, False, True). 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 (for example, 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.

Examples

The following examples use this worksheet.

This function returns 5:

MATCH(7600, B2:B7,1)

This function returns 2:

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

Note:

If an Invalid formula syntax warning appears, check whether the comma is set as the list separator in your regional settings on the Windows Control Panel. If a different character is set as the list separator, use this character in the above examples in place of the comma. (Windows XP: Access the Windows Control Panel and select Regional and Language Options. On the Regional Options tab page, click Customize. On the Numbers tab page, check which character is the default list separator on your computer. Use this character in the above examples instead of the comma.

Also, see