LOOKUP

In Filtering & data shaping

The LOOKUP function performs an approximate match lookup in one column and returns the corresponding value from another column.

Use this function when you need threshold-based or approximate mapping from one value range to another.

Parameters

Node1Node referenceRequired

Lookup Value. The value to search for in the index table.

Node2Node referenceRequired

Index. A column of data that is sorted in ascending order. The LOOKUP function searches for the value in this range.

Node3Node referenceRequired

Return Value. A range that contains the result.

DefaultReturnValueNumberOptional

The value returned when a lookup value is not found. Default: -1.

LevelNameLevel nameOptional

The level along which the index values are matched. Default: 'Sequence'.

Watch out

Example

Approximate threshold lookup

This example looks up a salary against an ordered threshold table and returns the corresponding tax value.

The lookup uses one node for the value to search, one node for the sorted index, and one node for the returned result.

Input node: 'Lookup Value'

Input node: 'Index'

Input node: 'Return Value'

Formula: LOOKUP('Lookup Value', 'Index', 'Return Value')

Expanding the sequence dimension

The sequence dimension is a default dimension that defines the steps of a time value. In rare cases, it can be helpful or even necessary to edit the sequence dimension to support complex scenarios on a monthly level.

Additional information on the sequence dimension and how to modify it can be found here: Configure the Sequence Dimension

See also

FILTER

When you want to filter rows by explicit criteria instead of mapping one value range to another through an ordered index table.

IF

When you want explicit conditional branching logic instead of using an approximate lookup table.