MATCH function

Returns the position that a given value appears at in the given lookup array, or returns an #N/A error if the value cannot be found. MATCH(30, { 10, 20, 30 }, 0)MATCH(30; { 10; 20; 30 }; 0) returns 3, because 30 is the third item in the array. MATCH(40, { 10, 20, 30 }, 0)MATCH(40; { 10; 20; 30 }; 0) returns an #N/A error, because 40 cannot be found.

Always provide the third parameter

If the third parameter is left out, MATCH tries to find an exact match for the value you specify, or failing that, the next smallest match. That is rarely what is intended, meaning that most invocations of MATCH must use 0 as the third parameter, which asks MATCH to only return exact matches.

The third parameter is known as the match mode, and it can also be set to a value that returns exact matches, or failing that, the next largest match. See below for more on this feature.

MATCH versus XMATCH

XMATCH is a modern replacement for MATCH, with more features and exact matches by default. Use it instead of MATCH if at all possible.

XMATCH provides the following features not provided by MATCH:

• The lookup array can be searched starting with the last element.
• If the lookup array is known to be ordered, a faster search mode can be used which speeds up searches significantly.
• There is a "fuzzy" search mode for text strings, enabling matches to start with a certain letter, for instance.

MATCH has one feature not offered by XMATCH, though: the ability to determine a match using a formula fragment. Here's an example:

The formula above returns the position of the first element which is both odd and is greater than 10. Use this feature of MATCH instead of XMATCH if you need the extra flexibility and ease of use offered by formula matching. This feature is offered by a different version of MATCH than the one documented here, which takes a different set of parameters — see below for more information on the other version.

Match modes

The third parameter to MATCH is the match mode, which determines which array elements are considered a match.

Finding exact matches

The third parameter to MATCH is the match mode. 0 requests an exact match. 0 is not the default, and must be specified explicitly if you need an exact match.

This formula returns 2, because 3 is the second element of the array:

This formula returns an #N/A error, because 2 cannot be found in the lookup array:

To check if a value is an #N/A error, use the ISNA function. This formula returns TRUE to indicate that the value cannot be found:

Finding exact matches or the next smallest value

Providing 1 as the match mode, which is the default, informs MATCH that an exact match or the next smallest value should be returned.

Both these formulas return 2, because 3 is part of the lookup array:

However, these two formulas don't return the same result:

The first formula (using a match mode of 0) returns an #N/A error to indicate that 2 cannot be found, whereas the second formula (using a match mode of 1) returns 1. It does so because the element 0 is found at position 1, which is the next smallest element.

MATCH can return an #N/A error even if the match mode is set to 1. Specifically, if there is no array element which is smaller than the sought value, #N/A is returned.

This formula returns #N/A, as no array element is smaller than the sought value, -10:

Finding exact matches or the next largest value

Providing -1 as the match mode informs MATCH that an exact match or the next largest value should be returned. This formula returns 2, referencing the array element 3 which is the next largest array element in the array:

This formula returns #N/A, as 100 cannot be found in the array, nor is there a larger element:

Examples

Returns the position of the first lookup array element located by a given formula fragment. MATCH(Element = 10, { 20, 15, 10 })MATCH(Element = 10; { 20; 15; 10 }) returns 3, because the first parameter identifies the element 10, which is the third element in the lookup array. MATCH(Element = 30, { 20, 15, 10 })MATCH(Element = 30; { 20; 15; 10 }) returns an #N/A error, because no element of the lookup array is equal to 30.

If you need to look up a value in a different array, consider using XLOOKUP instead of MATCH. XLOOKUP can also do matching using a formula fragment.

This version of MATCH is Calcapp-specific.

Finding matches with a formula fragment

Both these formulas return the position of the element 10 in the lookup array, 3:

The second formula is very different in that it has access to the full power of the formula language when determining which element of the lookup array matches. Consider this formula:

The formula above locates 15 in the lookup array, which is both odd and is greater than 10, and consequently returns 2. When determining what element matches, the first parameter has access to any operator and function, including the logical operators && (logical "and"), || (logical "or") and ! (logical negation), making for very powerful matching.

The formula fragment given as the first parameter is run once for every array element to be tested, and is expected to return TRUE if the element is a match, and FALSE otherwise. To do its work, it has access to the values Element (the array element currently being tested), Index (the numeric position of the array element currently being tested, starting at 1) and Source, the lookup array.

These values can be renamed using ->:

The formula sketch above renames Element to V, Index to I and Source to S.

Examples