Leading the way in Microsoft Office Development

Excel

MATCH(lookup_value, lookup_array [,match_type])

 -

MATCH worksheet function

Returns the position of a value in an array or list.

The "match_type" argument is very important as this determines if an exact match or a closest match is found.

If "match_type" = 0 then only exact matches are found.

If "match_type" = 1 then the list should be sorted in ascending order and the closest value less than (or equal to) "lookup_value" is returned.

If "match_type" = -1 then the list should be sorted in descending order and the closest value greater than (or equal to) "lookup_value" is returned.

If "match_type" is left blank, then it is assumed to be 1.

Searching for Matching Text (using *)

There is no distinction between uppercase and lowercase letters when matching text values.

An asterisk (*) matches any sequence of characters. This cannot be the first character.

This example returns the position of the first item that begins with the letter C.

When the "match_type" argument is 0 an exact match is found.

In this case "Cherry" is the first item, so 3 is returned.

When the "match_type" argument is 1 the closest value less than (or equal to) "lookup_value" is returned.

Assuming that the list is sorted in ascending order.

In this case "C*" does not exist so "Banana" is the closest value less than "C*", so 2 is returned.

If we were to change the value in cell "B5" to "C*" then this would return position number 3 since an exact match would be found.

When the "match_type" argument is -1 the closest value greater than (or equal to) "lookup_value" is returned.

Assuming that the list is sorted in descending order.

In this case "C*" does not exist so "Cherry" is the closest value greater than "C*", so 7 is returned.

Searching for Matching Text (using ?)

There is no distinction between uppercase and lowercase letters when matching text values.

A question mark (?) matches any single character. This cannot be the first character.

This example returns the first item that begins the letter "P" and has the letter "A" as its third character.

When the "match_type" argument is 0 an exact match is found.

In this case "Peach" is the first item, so 7 is returned.

When the "match_type" argument is 1 the closest value less than (or equal to) "lookup_value" is returned.

Assuming that the list is sorted in ascending order.

In this case "p?a*" does not exist so "Orange" is the closest value less than "p?a*", so 6 is returned.

When the "match_type" argument is -1 the closest value greater than (or equal to) "lookup_value" is returned.

Assuming that the list is sorted in descending order.

In this case "p?a*" does not exist so "Peach" is the closest value greater than "p?a*", so 3 is returned.

Searching for Matching Text (first character)

If you use the wildcard characters as the first characters in your "lookup_value" you must use a "match_type" value of 0.

If your "lookup_value" begins with an asterisk or a question mark and the "match_type" is not 0 the "lookup_value" is taken to be a literal string and therefore comes before the letter A.

This can be seen from the following examples.

Not Sorted - "match_type" = 1

This is still under construction.

Not Sorted - "match_type" = -1

This is still under construction.