how to return the latest text in the row or column with blank cells

[vijayaragavan]

job tracker list





A B C D E F G H I
1 LAST STATUS STATUS DATE 16/01/14 17/01/14 18/01/14 19/01/14 20/01/14 21/01/14
2 JOB-1 finish 20/01/14 ST-1 ST-1A finish
3 JOB-2 ST-2 21/01/14 ST-1 ST-2
4
5

In the above table,
in row 1, from column "D" are calender days.
in row 2, job status is updated for if there is any change in status.

the status will be updated regularly and if there is no progress, status is left blank

The "last status" (cell B2, B3 in this example) column should indicate the last/latest entry.
The "status date" (cell C2, C3 in this example) column should indicate the date corresponding to the last entry

This is to be done for every job in the rows

[bebo021999]

Try in C2:
=IFERROR(LOOKUP(2,1/($D2:$I2="finish"),$D$1:$I$1),"")
B2:
=IF(C2="","","finish")

[vijayaragavan]

i have attached the xl file along with this.

last status column should indicate not only the finish but anything which is latest.

and "status date" should indicate the corresponding date.

[AlKey]

Please see attache file

[daddylonglegs]

For last text value you can use this formula in B5 copied down

=LOOKUP("zzz",D5:N5)

and then for corresponding date in C5

=LOOKUP("zzz",D5:N5,D$3:N$3)

[vijayaragavan]

thank you very much for ur help and ur attached file.

you have made my job much easier to track.

[AlKey]

You're welcome and thanks for the feedback!

Please mark thread as "Solved" if your issue has been resolved. (Selecting Thread Tools-> Mark thread as Solved).