Look up data in a second workbook data element.

To learn how to apply the Lookup function without directly using the formula bar, visit Add Columns via Lookup.

Usage

Lookup(formula, local key 1..., external key 1…, local key 2..., external key 2…, ...)

formula [required] The formula to compute (or the target column to reference) for the row in the target element

local key [required] The column to use as a join key in the local data element

external key [required] The column to use as a join key in the target data element

Examples

Example #1

Lookup([Customers/Cust Name], [Cust Key], [Customers/Cust Key])

Data is inserted into the Sales table’s [Calc] column from 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.

Note: In formulas, columns from other tables should be referenced with the <table name>/ prefix (e.g. [Customers/Cust Name]). 

Screen_Shot_2021-11-03_at_3.23.30_PM.png

Example #2

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

Screen_Shot_2021-11-04_at_3.07.56_PM.png

Example #3

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.

Screen_Shot_2021-11-03_at_3.59.55_PM.png

See Also

Rollup
Add Columns via Lookup