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

JPlino 0
Viewed 46

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: Without values

Expected result:

Expected result

excel-formula excel
3 Answers

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".

Dominique0

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
useryPy710

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))

enter image description here

Tom Sharpe0
Related