MATCH

Excel Functions

Get the position of an item in an array

Syntax

EXCEL

=MATCH(lookup_value, lookup_array, [match_type])

Arguments

Parameter	Description
`lookup_value`	The value to match in lookup_array.
`lookup_array`	A range of cells or an array reference.
`match_type`	[optional] 1 = exact or next smallest (default), 0 = exact match, -1 = exact or next largest.

Return Value

A number representing a position in lookup_array.

Details

The MATCH function is used to determine the position of a value in a range or array. For example, in the screenshot above, the formula in cell E6 is configured to get the position of the value in cell D6. The MATCH function returns 5 because the lookup value ("peach") is in the 5th position in the range B6:B14: The MATCH function can perform exact and approximate matches and supports wildcards (* ?) for partial matches. There are 3 separate match modes (set by the match_type argument), as described below. Note: the MATCH function will always return the first match. If you need to return the last match (reverse search) see the XMATCH function. If you want to return all matches, see the FILTER function. MATCH only supports one-dimensional arrays or ranges, either vertical or horizontal. Howe

Examples

Example 1

The MATCH function is used to determine the position of a value in a range or array. For example, in the screenshot above, the formula in cell E6 is c

EXCEL

=MATCH(D6,B6:B14,0) // returns 5

Exact match

When match_type is zero (0), MATCH performs an exact match only. In the example below, the formula in E3 is:

EXCEL

=MATCH(E2,B3:B11,0) // returns 4

Exact match

In the formula above, the lookup value comes from cell E2. If the lookup value is hardcoded into the formula, it must be enclosed in double quotes (""

EXCEL

=MATCH("Mars",B3:B11,0)

Approximate match

When match_type is set to 1, MATCH will perform an approximate match on values sorted A-Z, finding the largest value less than or equal to the lookup

EXCEL

=MATCH(E2,B3:B11,1) // returns 5

Wildcard match

When match_type is set to zero (0), MATCH can use wildcards. In the example shown below, the formula in E3 is:

EXCEL

=MATCH(E2,B3:B11,0) // returns 6

Wildcard match

This is equivalent to:

EXCEL

=MATCH("pq*",B3:B11,0)

MATCH

Syntax

Arguments

Return Value

Details

Examples

See Also