MATCH

Syntax:

MATCH ( lookup-value , lookup-array [ , [ match-type ] ] )

Description: Locates the relative position of an array item that matches a specified value in a specified order. MATCH shall not distinguish between uppercase and lowercase letters when matching strings.

Arguments:

Name

Type

Description

lookup-value

number, string, logical, name, reference

The value to search for in lookup-array. If match-type is 0 and lookup-value is a string, the wildcard characters, question mark (?) and asterisk (*), can be used in lookup-value. A question mark matches any single character; an asterisk matches any sequence of characters. To locate a question mark or asterisk, precede that character with a tilde (~).

lookup-array

array, reference

A contiguous range of cells containing possible lookup values.

match-type

number

Specifies how lookup-value is matched with values in lookup-array, as follows:

Value	Meaning
-1	Finds the smallest value that is greater than or equal to lookup-value. The values in lookup-array shall be placed in "descending" order: `TRUE`, `FALSE`, Z–A, ..., 2, 1, 0, -1, -2, ...
0	Finds the first value that is exactly equal to lookup-value. The values in lookup-array can be in any order.
1 or omitted	Finds the largest value that is less than or equal to lookup-value. The values in lookup-array shall be placed in "ascending" order: ..., -2, -1, 0, 1, 2, ..., A–Z, `FALSE`, `TRUE`.

Return Type and Value: number – The relative position of an array item that matches a specified value in a specified order.

However, if

No match is found, #NUM! is returned.
match-type's value is out-of-bounds, #NUM! is returned.

example: