LOOKUP

Overview

Description	Performs an approximate match lookup in one column and returns the corresponding value from another column. Use when you need threshold-based or approximate mapping from one value range to another.
Syntax	`LOOKUP('Node1', 'Node2', 'Node3' [, DefaultReturnValue] [, "LevelName"])`
Parameters	Node1: Lookup Value. The value to search for in the index table. Node2: Index. A column of data that is sorted in ascending order. The `LOOKUP` function searches for the value in this range. Node3: Return Value. A range that contains the result. DefaultReturnValue: The value returned when a lookup value is not found. The system default is `-1`. LevelName: IndexLevel. By default, this is the level `'Sequence'`.
Limitations	Important: The values in the index must be placed in ascending order (alphabetical sorting), otherwise `LOOKUP` might not return the correct value. The `Index` values have to be sorted as `001`, `002`, `003`, ... instead of `1`, `2`, `3`, ... Otherwise, Valsight will consider, for example, `11` to be smaller than `2`. The index level should not be included in the lookup value table.

Examples

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

Employee	Salary
E1	50.000

Input node: Index

Sequence	Threshold
0	0
1	10.000
2	30.000
3	60.000

Input node: Return Value

Sequence	Tax
0	0
1	0
2	1.000
3	2.000
4	3.000

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

Employee	→ LOOKUP Result
E1	1.000

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

Related Functions

Function	When to use instead
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.