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 could look up the salary for each employee in a module based on grade and region. Then you could display the salaries in a results module. 

Syntax

Values to lookup[LOOKUP: Mapping, LOOKUP: Mapping 2]

同じ式で SUM と LOOKUP を同時に使用しないでください。これに従わなかった場合、計算時間が極めて長くなることがあります。詳細については、「式とモデルのパフォーマンスへの式の影響 」を参照してください。

Arguments

ArgumentData typeDescription
Values to lookupNumber, Boolean, date, time period, list, or text line item.

The data source to look up a value from. This can be a module line item or a list property, and can be in any data format.

The source can be in the following syntax: 

module.lineitem 

or 

list.property

MappingList, date, or time line item, or a property from the source.

The mapping is the cross reference criteria. It can be a line item with a data type of list or time period. It can also be a property from the source or a date data type. The mapping matches the source line item or property with the target line item. 

A common dimension must link the source and the mapping cross reference. See LOOKUP examples for more information.

You can enter multiple arguments as mappings. Arguments must be separated by a comma. For example: 

Pay Table.BasicPay[LOOKUP: Grade, LOOKUP: Region]

The LOOKUP function returns a result of the same data type as the Source argument. 

Note: In Classic, LOOKUP returns the value of the aggregate item in a composite hierarchy, and the default value for the line item in a non-composite hierarchy.

LOOKUP と時間軸

マッピング ライン アイテムのデータ型よりソース ライン アイテムのタイムスケールが細かい場合に LOOKUP を使用できます。また、マッピング ライン アイテムのディメンションよりもターゲット ライン アイテムのディメンションのタイムスケールが細かい場合にも LOOKUP を使用できます。

ただし、結果で使用するタイム スケールがソースにない場合、LOOKUP で 0 が返されます。 たとえば、結果で半年の合計を選択し、それをソースで選択しなかった場合、LOOKUP では 0 が返されます。

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 different from the target) and select the line item or list property heading. 
  3.  In the target module, type [LOOKUP: in the editor.
  4. Open the module that contains the mapping criteria, if different, and then select the line item to use for mapping. Either a:
    • List data type
    • Time period or date data type
    • List property

      You can also type the mapping criteria in the format module.lineitem or list.property.
  5. If you have multiple mapping criteria, separate them with a comma, and end with a  ]. For example, Pay Table.BasicPay[LOOKUP: Grade, LOOKUP: Region]

Calculation engine functionality differences

In Polaris, the LOOKUP function returns the value of the aggregate item in composite and non-composite hierarchies. The Classic Engine returns the value of the aggregate item in a composite hierarchy, and the default value for the line item in a non-composite hierarchy. 

In Polaris, you cannot use the LOOKUP function in a result line item with a time scale greater than the values you look up. The Classic Engine returns a value of 0 in this case.

In Polaris, if the target line item can't reference the mapping line item, then the LOOKUP is invalid.

Syntax example

Pay Table.BasicPay[LOOKUP: Grade, LOOKUP: Region]

Where:

  • Pay Table is the source module
  • BasicPay is a line item in the source module
  • Grade and Region are the dimensions in the source module and line items in the results module

Additional information 

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

Excel equivalent function

LOOKUP

SELECT

Examples

See LOOKUP examples.