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:

CodePudding user response：

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

CodePudding user response：

I don't understand your question (I have the idea that you have by mistake uploaded two times the same screenshot), but as far as I understand, you might need the Offset() worksheet function, which allows you to get values from other cells in other rows or columns, as in following example:

=OFFSET(D2,0,-2)

This takes the value, starting from cell "D2", stay on the same row but take two columns to the left. The result equals the value of cell "B2".

CodePudding user response：

If you have Xlookup, you can just do a reverse search:

=IF(C2="Epic","",XLOOKUP(IF(C2="Technical task","Story","Epic"),$C$2:$C2,$I$2:$I2,"Not found",0,-1))