Use the LOOKUP function to look up values in a source module or list and display the values in a target module. 

For example, you can look up the salary for each employee in a module based on grade and region and display them in a results module. 

Syntax

Values to look up[LOOKUP: Mapping, LOOKUP: Mapping 2, etc.]

Never use SUM and LOOKUP in the same formula. This can lead to extremely long calculation times. For more information, see Formulas and their effects on model performance .

Arguments

ArgumentData typeDescription
Values to look upNumber, Boolean, date, time period, list, or text line itemThe data source to retrieve a value from. This can be a module line item module.lineitem or a list property list.property, and can be in any data format.
MappingList, date, or time line item, or a property from the source

The cross-reference criteria used to match values between the source and target. It can be a line item with a data type of list, date, or time period, or a property from the source.

To provide multiple mappings, repeat this argument and separate each with a comma.

Note: For the mapping to work, a common dimension must link the source and the mapping cross-reference. For example, if you map by Grade, both the source module and the results module must share Grade as a dimension.

The LOOKUP function returns a result of the same data type as the Values to look up. 

Steps to use LOOKUP 

To use LOOKUP:

  1. In the target module, open the formula editor for the target line item.
  2. Open the source module (if it's different from the target module) and select the source line item or list property. 
  3.  In the formula editor, type [LOOKUP:.
  4. Open the module that contains the mapping criteria (if it's different), then select the line item to use for mapping. This can be either:
    • A list data type
    • A time period or date data type
    • A list property
      Alternatively, you can enter the mapping criteria manually using the format module.line item or list.property.
  5. If you have multiple mapping criteria, separate each with a comma, and close the formula with ]. For example, Pay Table.BasicPay[LOOKUP: Grade, LOOKUP: Region]

Calculation engine functionality differences

BehaviorClassicPolaris
In composite hierarchiesReturns the value of the aggregate itemReturns the value of the aggregate item
In non-composite hierarchiesReturns the default value for the line itemReturns the value of the aggregate item
In a result line item with a time scale greater than the source valuesReturns 0You can't use the LOOKUP function in this configuration

Syntax example

Pay table.Basic pay[LOOKUP: Grade, LOOKUP: Region]

Additional information 

For more information on when to use LOOKUP, see Formula usage tips.

Constraints

LOOKUP and time periods

You can use LOOKUP when the source line item is a finer timescale than the mapping line item data type. You can also use LOOKUP when the dimension of the target line item is a finer timescale than the dimension of the mapping line item.

If the time scale in the results doesn't exist in the source, LOOKUP returns 0. For example if you select Half-year totals in the results but it isn't selected in the source, LOOKUP returns 0.

Dimension matching (Polaris only)

In Polaris, a LOOKUP is invalid if:

  • The target line item can't reference the mapping line item.
  • The mapping line item has a dimension that isn't related to a dimension in the target line item. This includes cases where the mapping line item is dimensioned by a line item subset.

Excel equivalent function

LOOKUP

SELECT

Examples

Use the LOOKUP function to look up values in a source module or list and display the values in a target module using mapping criteria. These examples demonstrate how you can use LOOKUP in different scenarios in your models. Select each example to expand.