To use Lookup in a Flatlist


Hi All,


I have a scenario, as follows:


Module 1 : 

To get Headcount in this module


Dimensions: L6 hierarchy, L6 Flat List(same hierarchy as a flat list), Cost Centre

Line Item: HeadCount

Time by month


Module 2:

Dimensions: L6 Hierarchy, Grade, Contract Type

Line ITem: HeadCount

Time by month 


I wanted to get the headcount in module 1 by referring to module 2.the headcount in module 1 should be by the L6 flat list. ( Not by L6 hierarchy). Any inputs, please?






  • Use Find Item so that you have the appropriate flat list member.

    Then you can use Lookup to find the headcount associated with it

    Line Items:
    L6: ITEM(L6 Hierarchy)
    L6 Name: NAME('L6')
    Matching FL L6 List Member: FINDITEM(L6 Flat List,'L6 Name')
    Flat List HC: Flat List Module.Headcount[LOOKUP: 'Matching FL L6 List Member']
  • Hi all,

    doing as obriegr has suggested, the lookup function doesn't work for the parent list members of the hierarchy list.

    In other words, when the FINDITEM formula retrieves a list member which is a parent in the hierarchy list, the lookup function can't retrieve any value

    Do you have any additional suggestions?

    Thanks a lot