Mapping of child elements

Question

I can't find one good way to solve this problem. I will try to sum the issue as best as I can:

I use Hyperion Essbase Excel ad-in to navigate inside OLAP cubes. What I am trying to accomplish here is to map certain labels to values of a dimension. Inside this dimension are multiple levels of data grouping so one child node can have multiple levels of parents. I can know beforehand what will be the members but they are subject to change every month and finding missing members can be tedious so what we do is use the parent members and zoom in its members. However, inside one parent can be multiple different labels. I have achieved part of this by using VLOOKUP() and if the current row is not found in my table then I look one row down and drag that formula, this has the following undesirable effect:

I have numbered what goes together. As you can see in my example, the element "24566" is also found in the parent "FD120465". The result is that all the elements found before it in the same group are mapped to "Expense B" but the target result is that all child elements should be mapped to "Expense D" but "24566". Here is the formula I have used in this example:

=IFERROR(VLOOKUP(C11,$C$3:$D$8,2,0), D12)

There are several ways to deal with this that I have come up with but none that I like. I could make two different formulas, one that looks at only child elements and another that looks only at parent elements. If child elements are put first, it does not matter that the wrong label will be put in the group ("24566" would be labelled "Expense D" in the orange part). This also has the disadvantage to incur more work (defining the regions where to use formula 1 and formula 2 and either do the work manually or overkill it with VBA...)

Another way is to simply take out every child element and put it in the table with its proper mapping. The problem is that parents are subject to have more or less items in them each month and mapping can change (though that should not happen as often) so managing every child element will get tedious. The sample I have provided is tiny to what I have (15-20 children in a parent).

This is somewhat of a last shot I am giving at this task before we settle for the solution that has the least downsides. Is there an obvious (or even not so obvious) way to solve this problem cleanly ?

Thanks !

Answer 1

This works IF you want what I suspect you want and are starting from where I suspect you are. (It is not necessarily the best way to achieve the result it does from the data but may at least help in clarifying what is required.)

1. Add a helper column so that all children have a parent (assuming they do):

   B11=IF(LEFT(C11,2)="FD",C11,B12) (copied down as required)

2. =IFERROR(VLOOKUP(C11,$C$3:$D$8,2,FALSE),VLOOKUP(B11,$C$3:$D$8,2,FALSE)) (in D11, copied down as far as required).

I have assumed that the bottom two entries in the Table contain typos.