How to use “LOOKUP” function in Anaplan: Explanation through an example

aykcos · January 2022

Hello Anaplan Community,

During my Level-I and Level-II trainings I thought that it would be useful if an explanation with an detailed simulation for use of LOOKUP and SUM functions. There for I prepared a detailed explanation for each functions. Below you can find the explanation with an detailed simulation for use of LOOKUP function. I hope this will help you understand the use of LOOKUP function.

DETERMINING

There are several ways to pull data from one module to another. One of them is “LOOKUP” function. This function is possible under some conditions.

· Dimensionalizing according to one to many rule is needed,

· Target module and the source module have common dimension AND the source module has a parent list as a dimension of the target’s,

· The LOOKUP function works with the number, boolean, date, time period, list, or text line item values. But the source & target line item must have same format line item.

Mapping

A mapping table between the Source List and Target List can be set up in one of two places:

· in a module, using a list-formatted line item

· in a list, using a list-formatted property.

Syntax: Values to lookup[LOOKUP: Mapping 1, LOOKUP: Mapping 2, etc.]

Using the LOOKUP function requires correct mapping. Choosing the mapping module or mapping list according to the right criteria while performing mapping is important for the correct design of the formula.

If your mapping table contains the Target List and maps to a Source list-formatted line item or list-formatted property, then use LOOKUP in your formula. Setting your mapping table up this way says that each Target item can map to one and only one Source list item in the mapping table, so we are looking up that Source item value.

Note: If your mapping table contains the Source list and maps to a Target list-formatted line item or list-formatted property, then use SUM in your formula. Setting your mapping table up this way says that each Target item can map to many Source items, so we are summing up all Source data that is to land in each Target list item. This is similar to SUMIF in Excel.

Image by author (Aykut Coskun) made with PPTX

Let's consider our example. If we look at the image shown above, it can be seen the source model and target module have different dimensionality. Source module is “REV04”. We need “Revenue” data to be pulled into “REV05” module. . We already have this data in source module. which can be seen below.

If we look at the dimensions, it can be seen that the source module has “G2 Country” as a dimension. The target module has “G3 Locations” as a dimension, which is a child list of the “G2 Country”. This means that, for this dimension mapping is required (refer to Decision Algorithm box number 5). The source module has also the “P1 Product Family” list as a dimension.

On the other hand, the target module has the “P2 Products” list as a dimension, which is also a child of the “P1 Product Family”. Thus, “Revenue” data sorted by the “P1 Product Family” list is required to be granulated by “P2 Products” list. For this reason, we need mapping.

The formula will be like shown below.

Target Line Item Formula: ‘REV04.Revenue’[LOOKUP: SYS04.Country, LOOKUP: SYS06.Product Family]

Image by author Aykut Coskun made with PPTX “Decision Algorithm for Anaplan”

To see the article on "Decision Algorithm for a selection of correct functions in Anaplan" please click here.