LOOKUP does not work on list parents and only leaf items


When a LOOKUP is done on a parent item in a module, the result does not resolve to the column with that respective parent. Subsequently the value shows 0.
To reproduce:
1. Create list with leaf item and parent
2. Create module with column 1 as "Data", column 2 as "Parent", and column 3 as "Lookup", using list from 1. as the dimension.
3. In column 2, set formula to "PARENT(ITEM(List))"
4. In column 3, set formula to Data[LOOKUP:Parent]
Column 3 displays 0

29 votes

In Review · Last Updated


  • Status changed to: In Review
  • We have just encountered this a few times and have a workaround we think will work. But it's limited in use, especially when we are using large lists. We would be very interested in seeing this issue resolved.

  • we also need a solution to this problem

  • We are also experiencing this issue. Please add parents if I'm looking up anything in that chain!

  • Wanted to bring attention to this again to see if anyone has a better solution. I'm just hoping to be able to do lookups on the parent of a flat list, not even a full on hierarchy...


    Common example I'm experiencing:

    I have a list of Market Segments. The Top Level is "All Segments." I create a boolean dependent on the Market Segment. There are high level sales mgrs who have "All Segments." The boolean lookup doesn't work for them because the lookup won't recognize "All Segments."


    My work-around right now is a static IF statement at the start of each lookup for IF Segment = All Segments Then TRUE ELSE Boolean[Lookup: Segment].

    It's not great...

Get Started with Idea Exchange

See our Submission Guidelines and Idea Evaluation Criteria, then start posting your own ideas and showing support for others!