I can only use SELECT and not LOOKUP


I thought that SELECT and LOOKUP where basically the same thing and can be used interchangeably.

In the first ss I have a formula where I SELECT a facility. When I try to replace the SELECT with a LOOKUP, it give me an error.

The second ss is an image of the list hierarchy.

Why does this happen? Is there anything I can do or do I have to use SELECT? (I would prefer not to)



  • @Olek P

    Good question and it depends on how you have set up your list. First, SELECT is not dynamic as in you are hardcoding to that one member no matter what. The second issue, is if that list is marked as a Production list, meaning you can have different members in your DEV model than your Prod model, then you can't be sure the members will be the same in both environments. Another thing to think about is how the list is constructed, is it a Ragged list or a Composite list?

    • Ragged List - all members, including parents, are in one list
    • Balanced/Composite - all detailed members are at the same level (the bottom level) which also allows users to enter/store data aggregated levels (L2 or L3) which cannot be done in a Ragged list.

    Typically, and best practices state to only use SELECT on top members of lists, the ALL's or Total's or in your case the All Facilities as that is structural (assuming that is actually defined as a Top Level Member and not a "true" member) and will not change from DEV to PROD.

    Hope this helps,