Lookup matching cell in a table and return the value of the first cell that's merged upwards to the matching cell

Question

Good day,

I have a spreadsheet with a manually populated table (B:D).

Then, I'm attempting to create an extract from that table based on the values from column B according to the value from cell I2. In order to retrieve the values from column C, I used the following function in H4:

=FILTER(C:C,$B:$B=$I$2)

To get the corresponding Status from column D, I used the following function in I4:

=VLOOKUP(H4,C:D,2,FALSE)

Now, the part I'm missing is retrieving the associated subtopics and topics in the columns on the left – column G and F, values in blue are manually written but I want to automate it with a function based on the value from column H. The main challenge is, the cells that include topics and subtopics are merged across all those columns. As this is exemplary, the amount of updates varies in each subtopics. Each topic, subtopic and update is unique.

Therefore possibly the function needs to search for the unique update from column H in column C and go up until first cell without value and then go left to retrieve the subtopic? That's my guess. After this I also don't have any idea how to retrieve the topic.

Any help will be much appreciated!

Answer 1

We need to "flatten" or "normalize" the data. We can do that with two SCAN functions. Then we can stack them and filter the stacked arrays:

=LET(
    _rng,B2:D15,
    _b,TAKE(_rng,,1),
    _c,CHOOSECOLS(_rng,2),
    _tp,SCAN("",SEQUENCE(ROWS(_b)),
        LAMBDA(_z,_y,IF(AND(INDEX(_c,_y,1)="",INDEX(_c,MIN(_y+1,ROWS(_b)),1)=""),INDEX(_b,_y,1),_z))),
    _stp,SCAN("",SEQUENCE(ROWS(_b)),
        LAMBDA(_z,_y,IF(INDEX(_c,_y,1)="",INDEX(_b,_y,1),_z))),
    FILTER(HSTACK(_tp,_stp,CHOOSECOLS(_rng,2,3)),_b=I3)
    )