Take data from last row and copy to cell

[fearlessg1]

Hello all, excel noob here looking for some help after no luck with searching the web.
I have a spreadsheet that has progress comments for back and forth commenting. I am trying to have a cell that displays the last(newest) comment from the row for each line item. In my pasted example, I would like for item 1, J4 to have the AL4 text of "test 4".
And for item 2, cell J5 should have the text from Z5 of "test 2", and as more revision status updates are added, the latest revision update now show up for each item in the J column. I attached a picture to help visualize. Apologies if I am not explaining clearly.

 

[Fluff]

Hi & welcome to MrExcel.
How about

=LOOKUP(2,1/($K$2:$AN$2="Status"),K4:AN4)

 

[fearlessg1]

Firstly, thank you so much for the welcome and the help!
This works for the first item, where there is something in the status cell to the end, however for item 2, it is returning 0 since the comments ended at Z. Is there anyway to modify this to ignore the blanks and return the last "status" that had something in the cell?

 

[Fluff]

Oops, forgot part of it, try

=LOOKUP(2,1/(($K$2:$AN$2="Status")*(K4:AN4<>"")),K4:AN4)

 

[fearlessg1]

Oops, forgot part of it, try

=LOOKUP(2,1/(($K$2:$AN$2="Status")*(K4:AN4<>"")),K4:AN4)

You rock!!!! Thank you sir!

 

[fearlessg1]

1 more adder if you don't mind sir. Could an iferror or something similar be incorporated to leave the cell blank if the "status" is blank? Currently it returns a circular reference error and if the formula is copied down the cells, if the line has no status, it's mirroring the status of the line I drag down from. Hope I'm explaining clearly.

 

[Fluff]

If the formula is going in col J it will not return a circular reference. If you are getting that notification you will need to figure out why.

 

[fearlessg1]

If the formula is going in col J it will not return a circular reference. If you are getting that notification you will need to figure out why.

Your comment pointed me to what I did wrong. I moved the formula cell to N, but failed to modify the start point. I made that correction and it's now showing NA if the status is blank. Thank you!

 

[Fluff]

You can remove the #N/A error like

=IFNA(LOOKUP(2,1/(($K$2:$AN$2="Status")*(K4:AN4<>"")),K4:AN4),"")

obviously you will need to change the ranges to suit.