Lookup
The Lookup function finds matching data inside a workbook data element, either in the same table, or in an external table.
To understand how to apply the Lookup function without directly using the formula bar, see Add columns through Lookup.
All elements must be on the same data connection.
Syntax
Lookup(formula, local key 1, external key 1, [local key 2], [external key 2], ...)
This function has the following arguments:
- formula
- Required
- The formula to compute (or the target column to reference) for the row in the target element.
- local key 1
- Required
- The column to use as a join key in the local data element.
- external key 1
- Required
- The column to use as a join key in the target data element.
- local key 2
- Optional
- The additional column to use as a join key in the local data element.
- external key 2
- Optional
- The additional column to use as a join key in the target data element.
Notes
- The Lookup function can only reference one external data element at a time. If external keys from two different elements are referenced, Lookup will return the error
Rollup cannot reference more than one external relation
. To help avoid this error and more easily identify the source of external keys, Sigma recommends giving unique names to data elements.
Examples
Lookup with one external key
Lookup([Customers/Cust Name], [Cust Key], [Customers/Cust Key])
Data is inserted into the Sales tableβs [Calc] column from the Customer tableβs [Cust Name] column.
These two tables are joined using a single column (join key) from each table. In this case, both join keys are named [Cust Key].
The functionβs formula parameter directly references a column in the joined table.
In formulas, reference columns from other tables with the [table name/] prefix (e.g. ** [Customers/Cust Na).

Lookup with two external keys
Lookup([Customers/Zip Code], [Cust Name], [Customers/Name], [Cust Key], [Customers/Cust Key])
Data is inserted into the Ordered Items tableβs [Calc] column from Customer tableβs [Zip Code] column.
The functionβs formula parameter directly references a column in the joined Customers table.
These two tables are joined using two sets of join keys:
- Order Itemsβ [Cust Name] column is joined with Customerβs [Name] column;
- Order Itemsβ [Cust Key] column is joined with Customerβs [Cust Key] column

Lookup with an aggregate result
Lookup(Sum([Sales Amounts/Sales Amount]), [Order Number], [Sales Amounts/Order Number])
Data is inserted into the Orders tableβs [Calc] column from Sales Amount tableβs [Sales Amount] column.
The functionβs formula parameter uses the Sum function to aggregate values from the Sales Amount tableβs [Sales Amount] column. For each order in Orders, the Lookup function returns the sum of all [Sales Amount] values with that [Order Number].
These two tables are joined using a single set of join keys: Ordersβ [Order Number] column is joined with Sales Amountsβ [Order Number] column.

Updated 12 days ago