Find Last Status in Range

Hi,

I have column L39:L43 with various statuses, such as:

IP = In Progress
NS = Not Started
TC = Task Completed
TBD = To Be Determined

In Cell 38, I would like a function that will either put "IP" if subtasks are in progress or if the "last" subtask is "TC", then put "TC"

Currently, I have:

=IF(L39="","",IF(L43<>$B$19,"IP",IF(L43=$B$19,$B$19)))

Problem with this that my last cell may vary because I have 5 subtasks & I may not need all of them.

How can I modify this function to find the last "TC" in the range of cells?

Thanks

try a solution for the cell 38

=IF(LOOKUP(REPT("z",255),L39:L43) ="TC","TC","IP")

Suggest you upload a sample workbook (no sensitive data) that shows the desired results and which contains an explanation of your requirements. A more precise solution can be given.

Hi tigertiger,

What the heck is this? Whatever it is, it works!!!

Thank you.....

it is easy to understand,
LOOKUP(REPT("z",255),L39:L43)

To get the value of the last cell in the range that (the cell) is not empty