MATCH

MATCH(lookup_value, lookup_array, reverse_search=False)

The MATCH function searches for a specified value within an array and returns its 1-based index. If the value is not found, the formula evaluates to an error value. An optional argument reverse_search can be provided; if set to True, the search begins at the end of the array and moves backwards. If the array contains duplicates, MATCH returns the first occurrence. If any argument is blank or in an error state, the formula evaluates to an error.

Parameters:

• lookup_value (Operand | int | float | str | bool) –

  The value to search for in lookup_array. Must be compatible with the array elements. If lookup_value is blank or in an error state, or the value is not found, the returned formula will evaluate to an error.

  Supported types:

  • INTEGER

  • DECIMAL

  • STRING

  • BOOLEAN

  • DATE

  • DATETIME

  • TIME

  • OBJECT

• lookup_array (Operand) –

  The array in which to search for lookup_value. Must be a valid array type and compatible with the data type of lookup_value. If the array is blank or in an error state, the formula will evaluate to an error.

  Supported types:

  • INTEGER_ARRAY

  • DECIMAL_ARRAY

  • STRING_ARRAY

  • BOOLEAN_ARRAY

  • DATE_ARRAY

  • DATETIME_ARRAY

  • TIME_ARRAY

  • OBJECT_ARRAY

• reverse_search (bool | Operand) –

  Optional. If True, the search starts from the end of the array and moves backward. Defaults to False. Only BOOLEAN values are accepted; otherwise, a ValueError is raised.

  Supported types:

  • BOOLEAN

Return type:

Formula

Returns:

The 1-based index of the array where the match is found. If the value is not found, the formula will evaluate to an error.

Supported types:

• INTEGER

Raises:

ValueError – if the data type of lookup_value is not a singular version of the data type of lookup_array.

Examples

Basic usage:

MATCH(5, [1, 3, 5, 7])
# Returns 3

Using reverse search:

MATCH(5, [1, 3, 5, 7], reverse_search=True)
# Returns 2