Lookup

The Lookup function finds matching data inside a workbook data element, either in the same table, or in an external table.

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.

Examples

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 Name]).

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(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.

These two tables are joined using a single set of join keys: Orders’ [Order Number] column is joined with Sales Amounts’ [Order Name] column.