Search

LOOKUP

The function looks up a number, Boolean, time period, list item, text, or date value in a list or a time period from a source module using one or more common mappings.

Mapping matches:

  • a list-formatted line item,
  • a time period-formatted item, or
  • property from the source

with a dimension of the target line item. The function uses the common dimensions between the source component (list or module) and result module and returns the content to the result module field.

A dimension may be a Row, Column, or Page, or a line item in any of those three.

To determine whether to use SUM or LOOKUP, read the page on Formula Usage Tips.

Back to Top

Syntax

x[LOOKUP: y] where:

  • x: Source: Number, Boolean, date, time period, list, text
  • y: Mapping: List, time period
Note: This function requires one and accepts multiple arguments as mappings—each argument must be separated by a comma.

Format

Input Format Output Format

x: Number, Boolean, date, time period, list, text

y: List item, time period

Matches the data format used by argument x

Arguments

The function

Source.Item[LOOKUP: Mappinga(, LOOKUP: Mappingb, LOOKUP: Mappingc)]

uses the following arguments:

Source.Item

Argument Format Cell Format Remarks
Module.line item List-formatted The data type of the result line item must match the data type of the source line item
List.Property Matches the property format List providing data
Time period The time period formatting for the mapped time period in the result must match source time periods. The timescale applied to the mapping must match the timescale applied to the result, OR If mixed timescales used, the timescale applied to the result must be at coarser granularity than the timescale applied to the mapping. For valid mixed timescales between mapping and result, the mapping must have a non-default time summary method selected.

Mapping

  Dimensionality Remarks
Module.line item Line item from Result module Value to reference

Back to Top

Constraints

The function has the following constraints:

  • The dimensions of the mappings must be dimensions of the result line item.
  • The data type of the result line item and the source line item must be the same.
  • For valid mixed timescales between mapping and result, the mapping must have a non-default time summary method selected.
  • LOOKUP does not work with non-composite lists (lists with no defined Parent hierarchy).

Excel equivalent

Back to Top

Example


Back to Top

LOOKUP

Suppose you want a results module that allows you to look up the salary for each employee in a pay table source module based on the grade and region. In this example, Region and Grade are both list-formatted items in the results module, but are dimensions in the source pay table:

Result = Pay Table.Basic Pay[LOOKUP: Grade, LOOKUP: Region]

The source module has Grade and Region as dimensions:

The results module contains Grade and Region as list-formatted line items:

For the results module, LOOKUP uses the mappings for Grade and Region to return employee salaries from source module data. In this example, the line items of the results module are shown as columns:


Using a single mapping - list

Data for the example function

Grades List

Grade data is contained in a list.

Salary

List Property

Number Format

Grade 1 10000
Grade 2 20000
Grade 3 30000
Grades List Properties tab

Grades List Grid View showing Salary Data

Employee Salaries Module

The Employee Salaries Module is configured using:

Columns
  • Grade as a line item list-formatted to Grade list and
  • Salary as a line item-formatted to number.
Rows
  • Employees list

The Salary column uses the following function:

Grades.Salary[LOOKUP: Grade]

How the function works in this example

The LOOKUP function in the Salary column looks up the Grade on the same row, locates the row with matching value in the Grade List and returns the Salary.

Using a single mapping - Module

Set up the example data

Grade and Employee data is contained in lists.

  • The Pay Table module is configured using:

Columns

  • Line item Salary formatted as number

Rows

  • Grades list
Pay Table

Salary

List Property

Number Format

Grade 1 10000
Grade 2 20000
Grade 3 30000
  • The Employee Salaries module is configured using:
    • Columns
      • Grade: Line item list-formatted to Grades list
      • Salary: Line item-formatted to number
    • Rows - Employee list
Employee Salaries Module
Grade Salary (using LOOKUP Function)
Person A Grade 2 20000
Person B Grade 3 30000
Person C Grade 1 10000
Person D Grade 2 20000

The Salary column uses the following function:

Pay Table.Salary[LOOKUP: Grade]

How the function works in this example

The function in the Employee Salaries module – Salary column reads the Grade value in the row, then returns the Salary value from the Pay Table module for that Grade.

The Employee Salaries module and the Pay Table module both use the Grade list that provides the common dimension.

Using Multiple Mappings

Set up the example data

Pay table
Version - Salary(, Allowed Expenses, Commission)
Region A Region B Region C Region D
Grade 1 7000 8000 9000 10000
Grade 2 10000 11000 12000 13000
Grade 3 12000 14000 15000 16000
Grade 4 15000 16000 17000 18000

Use the Pay Table to LOOKUP an employee Salary

The Salary column has this formula

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

How the function works in this example

The function uses the Salary Version of the Pay Table module to return the value at the intersection of the Grade row and Region column using the values from the source row.

Employee, Grade, and Region data is contained in lists.

  • The Pay Table module is configured as follows;
    • Columns: Region list
    • Rows: Grade list
    • Pages: Line items: The line items list contains the Salary item.
  • The Employee Salaries module is configured as follows;
    • Columns: Two list-formatted line items named Grade and Region. Each is formatted to use the respective list. A third line item titled Salary is also included that contains the LOOKUP function.
    • Rows: Employee list
Salary Module
Grade Region Salary (using Lookup Function)
Person A 2 C 22000
Person B 3 B 31000
Person C 1 A 10000
Person D 2 A 20000

LOOKUP an item from a list

Function location - Zip Code field

AccountL.Zip Code[LOOKUP: AccountM]        

Set up the example data

Source list

List of Accounts

Source List Properties

  • Zip Code – List-formatted to the Zip Code list
Result module
  • AccountM field – List-formatted to Account list
  • Zip Code Field – List-formatted to Zip Code list

Operation

Reads to the target AccountL list and returns the Zip Code associated with the value from the source AccountM field.

LOOKUP an item from a module

Function location – Role Type field

Role.Roles[LOOKUP: Plan Type]
Source

Role Module

  • Rows:
    • Plans list
  • Columns:
    • Roles as list-formatted line item
Result Module
  • Columns:
    • Role Type field: List-formatted to Role; Holds the Formula

    • Plan Type field: List-formatted to Plans list
  • Rows
    • Employees list

Back to Top

Use LOOKUP with time period

Constraints

  • The dimensions of the mappings must be dimensions of the result line item.
  • The list format of each mapping must match one of the dimensions of the source line item.
  • The data type of the result line item must match the data type of the source line item.
  • If you use a time period mapping in a LOOKUP function, the time period formatting for the mapped time period in the result must match source time periods.

    Mapping/Result timescales:

    • The timescale applied to the mapping must match the timescale applied to the result, OR
    • If mixed timescales used, the timescale applied to the result must be at coarser granularity than the timescale applied to the mapping.

    For valid mixed timescales between mapping and result, the mapping must have a non-default time summary method selected.

Set up the example data

Source

PayRegion module

  • Pages
    • Line items - Pay
  • Rows:
    • Regions list
  • Columns:
    • Time - Model Setting
Result Module

Staff Costs module

  • Columns:
    • Line items:
      • Employee List-formatted to Employee list
      • Region List-formatted to Regions list
      • Period formatted to Month
      • Costs formatted to Number - Holds the formula
  • Rows
    • Transactions list

USING THE FUNCTION

The Costs column uses the following function:

PayRegion.Pay[LOOKUP: Region, LOOKUP: Period]

HOW IT WORKS

The Staff Costs results module for displaying salary costs by region and period is pivoted to show line items as columns.

The Period line item is time period-formatted and the period granularity matches the period values used in the PayRegion source module (Month). We can then use LOOKUP in the Staff Costs line item formula to bring in the data values directly from the source module into the result line item.

Back to Top

USING LOOKUP WITH NUMBERED LISTS

If you use a numbered list in a module you will need a LOOKUP to pull data in, and a SUM function to pull data out. A numbered list contains items with a system-generated ID numbers and an optional display name. The display names need not be unique, but the underlying ID numbers are. When you insert a new numbered list item, it is automatically assigned the next ID number that is never re-used, even if items are deleted and reinserted. The ID number can be hidden and one of the other properties chosen as the display name. Properties can contain formulas, to allow the display name to be a concatenation of properties or whatever is needed to make the label meaningful.

For example, suppose you have a numbered list named #Resources that has a list-formatted property looking up the Employees List, to pull data into the Project Days module, that has a dimension of #Resources, use a LOOKUP.

PULL DATA INTO MODULES WITH NUMBERED LISTS

Set up the example data

Days Available = People Details.Days Available[LOOKUP: #Resources.People]        

HOW IT WORKS

The Project Days module uses the value imported from the #Resources list to LOOKUP the Days Available value in the People Details module.

#Resources is a numbered list with has a Property named Employees that is list-formatted on the Employee list. Each entry in the #Resources list is uniquely referenced by the associated list number. The LOOKUP function uses the list number to obtain the Employees property value, that is used to return the Days Available value from the People Details module. The Project Days module shows the Display Name property if it is configured in the #Resources list, otherwise it shows the list number.

A direct relationship (using the formula People Details.Days Available) is not a valid formula because the Employees list does not have a built in top level to which the Project Days module can connect. LOOKUP allows a comparison of values between the modules and lists.

Back to Top

LOOKUP and Non-Composite Lists

LOOKUP is not designed to work with the aggregate items on non-composite lists (lists with no parent hierarchy defined). This can be seen in the following example.

The Sales Employees list nominates the parent for each employee in the Parent column.

The list does not have a Parent Hierarchy specified.

The LOOKUP Non-composite List Example module uses the Sales Employees list as rows and has three line items as columns.

The following formulas are visible in Blueprint.

The formula for the Parent 1 line item is:

PARENT(ITEM(Sales Employees))

The formula for the Parent Descendants line item is:

Descendants[LOOKUP:Parent 1]

We can also see that the line items are set up in the following way:

  • Descendants is number-formatted with no formula applied.
  • Parent 1 is list-formatted on the Sales Employee list and returns the parent for each item in that list.
  • Parent Descendants is number-formatted and uses LOOKUP with the Parent 1 line item as mapping. However, values are not returned because LOOKUP only works with leaf items from the non-composite Sales Employees list.

If you wish to use the LOOKUP function, you must convert non-composite lists to lists or modules in which the Parent Hierarchy is defined.

A second module uses a Sales Persons composite list, that has the Sales Managers list defined as its Parent Hierarchy, as a dimension on rows and line items on columns:

As seen in Blueprint:

We can see that the line items are set up in the following way:

  • Descendants is number-formatted with no formula applied.
  • Parent 1 is list-formatted on the Sales Managers list and returns the item in the Sales Managers list that is the parent of items in the Sales Persons list.
  • Parent Descendants is number-formatted and uses LOOKUP with the Parent 1 line item as mapping. In this case, values are returned because the Parent 1 items are true parent items belonging to the Sales Managers list defined as the parent hierarchy.

Back to Top

Similar functions