Microsoft Office Tutorials and References

In Depth Information

**Syntax**

Syntax

MATCH(lookup_value,lookup_array,match_type)

The MATCH function returns the relative position of an item in a column of

values. It is useful for determining if a certain value exists in a list.

The MATCH function takes the following arguments:

•
lookup_value

lookup_value
—
This is the value you use to find the value you want

in a table. lookup_valuecan be a value, which is a number, text, or

logical value or a cell reference to a number, text, or logical value.

•
lookup_array

lookup_array
—
This is a contiguous range of cells that contains

possible lookup values. lookup_arraycan be an array or an array

reference.

•
match_type

match_type
—
This is the number
–
1, 0, or 1. Note that you can use

TRUE instead of 1 and FALSE instead of 0. match_typespecifies how

Microsoft Excel matches lookup_valuewith values in look-

up_array. If match_typeis 1, MATCH finds the largest value that is

less than or equal to lookup_value. lookup_arraymust be placed in

ascending order, such as ...
–
2,
–
1, 0, 1, 2,...; A
–
Z; or FALSE, TRUE. If

match_typeis 0, MATCH finds the first value that is exactly equal

to lookup_value. lookup_arraycan be in any order. If match_typeis

–
1, MATCH finds the smallest value that is greater than or equal to

lookup_value. lookup_arraymust be placed in descending order, such

as TRUE, FALSE; Z
–
A; or ...2, 1, 0,
–
1,
–
2,.... If match_typeis omitted,

it is assumed to be 1.

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 re-

turns an #N/A error.

If match_typeis 0 and lookup_valueis text, lookup_valuecan contain the

wildcard characters asterisk (*) and question mark (?). An asterisk matches

any sequence of characters; a question mark matches any single character.