LOOKUP

LOOKUP(table, field_name, condition, reverse_search=False)[source]

The LOOKUP function searches for a row in a table or object array where a specified field matches a given condition and returns the corresponding row. If no matching row is found, the formula evaluates to an error. An optional reverse_search argument can be provided; if True, the search begins from the end of the table and moves backwards. Only exact matches are considered. If any argument is blank or in an error state, the formula evaluates to an error.

Parameters:

  • table (Operand) – The table or OBJECT_ARRAY in which to perform the lookup. If the input is blank or in an error state, the formula evaluates to an error.

  • field_name (Operand | str) – The field name of the column to match against the condition. Must exist in table. If the field does not exist, the formula evaluates to an error. If a raw string is provided (rather than a formula), the method verifies that the field exists in the table, and raises a ValueError otherwise.

  • condition (Operand | int | float | str | bool) – The condition value to search for within the specified column. Must be compatible with the column’s data type. If the value is not found the formula evaluates to an error.

  • reverse_search (bool | Operand) – Optional. If True, the search starts from the last row and moves backward toward the first row. Defaults to False. Only BOOLEAN values are accepted; otherwise, a ValueError is raised.

Return type:

Formula

Returns:

The row in the table where the specified field equals the condition. If no row matches, the formula evaluates to an error.

Raises:

  • ValueError – if the specified field does not exist in the table.

  • ValueError – if the data type of condition is incompatible with the field’s data type.

  • ValueError – if table is not a valid TABLE or OBJECT_ARRAY.

  • ValueError – if reverse_search is not BOOLEAN.

Examples

Basic usage:

LOOKUP(my_table, "status", "active")
# Returns the first row where status == "active"

Using reverse search:

LOOKUP(my_table, "status", "active", reverse_search=True)
# Returns the last row where status == "active"

``table`` — accepted types

  • OBJECT_ARRAY

``field_name`` (literal) — accepted types

  • STRING

``condition`` — accepted types

  • BOOLEAN

  • BOOLEAN_ARRAY

  • BOOLEAN_MAP

  • DATE

  • DATETIME

  • DATETIME_ARRAY

  • DATETIME_MAP

  • DATE_ARRAY

  • DATE_MAP

  • DECIMAL

  • DECIMAL_ARRAY

  • DECIMAL_MAP

  • INTEGER

  • INTEGER_ARRAY

  • INTEGER_MAP

  • NULL

  • OBJECT

  • OBJECT_ARRAY

  • STRING

  • STRING_ARRAY

  • STRING_MAP

  • TIME

  • TIME_ARRAY

  • TIME_MAP

``reverse_search`` — accepted types

  • BOOLEAN

Return types

  • OBJECT