LOOKUPARRAY

LOOKUPARRAY(source_array, match_array, result_array)[source]

Performs a vectorised mapping on source_array defined by match_array and result_array.

Each element in source_array is searched for in match_array. If a match is found, the corresponding element from result_array is returned. If no match exists, the result is null.

The match_array and result_array arguments must be of equal length, defining a mapping from match → result. The first argument (source_array) can be a different length; the output will have the same length as source_array and the same data type as result_array.

This function is similar to a vectorised VLOOKUP in Excel or dictionary mapping: it applies the mapping to every element in the source array in one operation.

Parameters:

source_array (Operand) –
The array containing values to look up. Must be an array type of the same type as match_array. Each element will be searched for in match_array.

Supported types:
- INTEGER_ARRAY
- DECIMAL_ARRAY
- STRING_ARRAY
- BOOLEAN_ARRAY
- DATE_ARRAY
- DATETIME_ARRAY
- OBJECT_ARRAY
match_array (Operand) –
The array containing reference values. Each element in source_array is compared to this array to find a match. Must be the same type as source_array and the same length as result_array.

Supported types:
- INTEGER_ARRAY
- DECIMAL_ARRAY
- STRING_ARRAY
- BOOLEAN_ARRAY
- DATE_ARRAY
- DATETIME_ARRAY
- OBJECT_ARRAY
result_array (Operand) –
The array containing values to return when matches are found in match_array. Must have the same length as match_array.

Supported types:
- INTEGER_ARRAY
- DECIMAL_ARRAY
- STRING_ARRAY
- BOOLEAN_ARRAY
- DATE_ARRAY
- DATETIME_ARRAY
- OBJECT_ARRAY

Return type:

Formula

Returns:

A formula object representing the lookup operation. The resulting array has the same length as source_array, with each element computed as follows:

If source_array[i] is found in match_array, return result_array[j], where match_array[j] == source_array[i].

If source_array[i] is not found in match_array, return null.

Supported types:

INTEGER_ARRAY
DECIMAL_ARRAY
STRING_ARRAY
BOOLEAN_ARRAY
DATE_ARRAY
DATETIME_ARRAY
OBJECT_ARRAY

Raises:

ValueError – If any input is not an array type, if source_array and match_array have different types, or if match_array and result_array have different lengths.

Examples

Basic usage with strings:

LOOKUPARRAY(["A", "B", "C"], ["X", "B", "Y"], ["X_val", "B_val", "Y_val"])
# Returns [null, "B_val", null]
# Explanation:
# - "A" is not in match_array → null
# - "B" is found at index 2 → return "B_val"
# - "C" is not in match_array → null

Numbers with exact matches:

LOOKUPARRAY([1, 2, 3], [3, 1, 2], [30, 10, 20])
# Returns [10, 20, 30]
# Explanation:
# - 1 is found at index 2 → 10
# - 2 is found at index 3 → 20
# - 3 is found at index 1 → 30

Partial matches:

LOOKUPARRAY([5, 7, 9], [7, 5], [70, 50])
# Returns [50, 70, null]
# Explanation:
# - 5 is found at index 2 → 50
# - 7 is found at index 1 → 70
# - 9 is not found → null

Boolean arrays:

LOOKUPARRAY([True, False, True], [False, True], ["No", "Yes"])
# Returns ["Yes", "No", "Yes"]
# Explanation:
# - True is found at index 2 → "Yes"
# - False is found at index 1 → "No"
# - True again → "Yes"
Basic usage: