Excel – How to get the previous value on a column with an if statement?

I am trying to automatically pair Issue ID’s (Column I) to their parents filling the Column J.

My structure is:

Epic
- Story
-- Technical task

Stories need to be parented to Epics, and Technical tasks need to be parented to Stories.

I need a formula that:

For Stories: Search on Column C for the previous "Epic" and fill the specific "Story" cell on Column J with the value of that "Epics" from Column I.

For Technical tasks: Search on Column C for the previous "Story" and fill the specific "Technical task" cell on Column J with the value of that "Story" from Column I.

What I have:

Expected result:

>Solution :

You can use this formual in J – if you have Excel 365:

=IF(C2<>"Epic",
LET(lookFor,IF(C2="Technical task","Story","Epic"),
f,FILTER($I$2:$I2,$C$2:$C2=lookFor),
INDEX(f,COUNTA(f))),"")

• lookfor returns the parent issue type
• then the ID column is filtered to only return the according issue types IDs above the current row
• then the last of the returned IDs is given