MATCH function

MATCH(soughtValue, lookupArray, matchMode?) MATCH(soughtValue; lookupArray; matchMode?)

soughtValue — Number, Logical, Text, Color, { Number }, { Logical }, { Text } or { Color }

The value to look for in the lookup array.

lookupArray — { Number }, { Logical }, { Text } or { Color }

The array which may or may not contain the sought value.

matchMode — Number or { Number } (optional)

0 for an exact match, -1 for an exact match or the next smallest value or 1 for an exact match or the next largest value. If omitted, -1 is assumed, meaning that an exact match, or the next smallest value, is needed. Due to this default, and the fact that the exact value is sought most of the time, this parameter should be set explicitly, without relying on the default match mode. XMATCH provides exact matches by default.

Returns — Number or { Number }

The position where the sought value appears in the given lookup array, or an #N/A error if the value cannot be found.

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:

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

MATCH(ISODD(Element) && (Element > 10), { 1, 5, 10, 15 })MATCH(ISODD(Element) && (Element > 10); { 1; 5; 10; 15 })

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:

MATCH(3, { 0, 3, 4, 6 }, 0)MATCH(3; { 0; 3; 4; 6 }; 0)

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

MATCH(2, { 0, 3, 4, 6 }, 0)MATCH(2; { 0; 3; 4; 6 }; 0)

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:

ISNA(MATCH(2, { 0, 3, 4, 6 }, 0))ISNA(MATCH(2; { 0; 3; 4; 6 }; 0))

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:

MATCH(3, { 0, 3, 4, 6 }, 0)MATCH(3; { 0; 3; 4; 6 }; 0)
MATCH(3, { 0, 3, 4, 6 }, 1)MATCH(3; { 0; 3; 4; 6 }; 1)

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

MATCH(2, { 0, 3, 4, 6 }, 0)MATCH(2; { 0; 3; 4; 6 }; 0)
MATCH(2, { 0, 3, 4, 6 }, 1)MATCH(2; { 0; 3; 4; 6 }; 1)

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:

MATCH(-10, { 0, 3, 4, 6 }, 1)MATCH(-10; { 0; 3; 4; 6 }; 1)

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:

MATCH(2, { 0, 3, 4, 6 }, -1)MATCH(2; { 0; 3; 4; 6 }; -1)

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

MATCH(100, { 0, 3, 4, 6 }, -1)MATCH(100; { 0; 3; 4; 6 }; -1)

Examples

MATCH(30, { 10, 20, 30 }, 0)MATCH(30; { 10; 20; 30 }; 0)

Returns 3, because 30 is the third element of the array. The match mode, 0, is specified explicitly, because the default match mode (-1) calls for the exact value to be returned, or failing that, the next smallest value, which is rarely what is needed. XMATCH provides exact matches by default.

MATCH(40, { 10, 20, 30 }, 0)MATCH(40; { 10; 20; 30 }; 0)

Returns #N/A, because 40 is not part of the array.

ISNA(MATCH(40, { 10, 20, 30 }, 0))ISNA(MATCH(40; { 10; 20; 30 }; 0))

Returns TRUE, because MATCH returns #N/A as 40 is not part of the array.

MATCH(40, { 10, 20, 30 }, 1)MATCH(40; { 10; 20; 30 }; 1)

Returns 3, which is the position of 30 in the array. The third parameter, -1, requests the match mode "exact or next smallest," which returns the next smallest match if the sought value cannot be found.

MATCH(5, { 10, 20, 30 }, 1)MATCH(5; { 10; 20; 30 }; 1)

Returns #N/A, because 5 cannot be found in the array, nor is there a smaller value. The third parameter, -1, requests the match mode "exact or next smallest," which returns the next smallest match if the sought value cannot be found.

MATCH(5, { 10, 20, 30 }, -1)MATCH(5; { 10; 20; 30 }; -1)

Returns 1, which is the position of 10 in the array. The third parameter, 1, requests the match mode "exact or next largest," which returns the next largest match if the sought value cannot be found.

MATCH(40, { 10, 20, 30 }, -1)MATCH(40; { 10; 20; 30 }; -1)

Returns #N/A, because 40 cannot be found in the array, nor is there a larger value. The third parameter, 1, requests the match mode "exact or next largest," which returns the next largest match if the sought value cannot be found.

INDEX({ 2, 4, 8 }, MATCH(Field1, { 1, 2, 3 }, 0))INDEX({ 2; 4; 8 }; MATCH(Field1; { 1; 2; 3 }; 0))

Returns 2 if the value of Field1 is equal to 1, 4 if it is equal to 2 and 8 if it is equal to 3.

XLOOKUP(Field1, { 1, 2, 3 }, { 2, 4, 8 })XLOOKUP(Field1; { 1; 2; 3 }; { 2; 4; 8 })

Returns 2 if the value of Field1 is equal to 1, 4 if it is equal to 2 and 8 if it is equal to 3. Combining INDEX and MATCH is often equivalent to using XLOOKUP.

MATCH({ 20, 30, 40 }, { 10, 20, 30 }, 0)MATCH({ 20; 30; 40 }; { 10; 20; 30 }; 0)

Returns the array { 2, 3, NA() }{ 2; 3; NA() }, which are the three positions at which 20, 30 and 40 appear in the { 10, 20, 30 }{ 10; 20; 30 } array. (As 40 does not appear, the third element of the return array is an #N/A error.) By specifying an array as the first parameter, multiple results can be returned.

MATCH(itemLocator, lookupArray) MATCH(itemLocator; lookupArray)

itemLocator — Logical

A formula fragment which is invoked once for every array element being tested, and is expected to return TRUE if the element matches 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.

lookupArray — { ? }

The array which may or may not contain the sought value.

Returns — Number

The position of the first lookup array element located by a given formula, or an #N/A error if no element can be found.

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:

MATCH(10, { 20, 15, 10 })MATCH(10; { 20; 15; 10 })
MATCH(Element = 10, { 20, 15, 10 })MATCH(Element = 10; { 20; 15; 10 })

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:

MATCH((Element > 10) && ISODD(Element), { 20, 15, 10 })MATCH((Element > 10) && ISODD(Element); { 20; 15; 10 })

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

MATCH((V, I, S) -> ...)MATCH((V; I; S) -> ...)

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

Examples

MATCH(Element = 10, { 20, 15, 10 })MATCH(Element = 10; { 20; 15; 10 })

Returns 3, the position of 10 in the lookup array,

MATCH(I -> (I > 10) && ISODD(I), { 15, 10 })MATCH(I -> (I > 10) && ISODD(I); { 15; 10 })

Returns 1, the position of the first item in the lookup array which is both greater than 10 and is an odd number.