I need to find the last occurance of text in a range of cells. The
range can have empty cells and various text values. This is a project
staff schedule and I want to have a formula that will display the Last
Sick Day and Next Vacation Day.

Column A, Row 2 contains the consultant name
Row 1 contains the date (each day from project start to project end)
Intersecting sells contain "MC" for a sick day and "H" for a vacation
day or blank for neither.

In laymans terms:
For Last Sick Day I want to look at today's date, search across Row 5
to find that date, go backwards in time (columns) to find the cell
reference for the last occurance of "MC" and return the date value from
Row 5.

For Next Vacation Day I want to look at today's date, search across Row
5 to find that date, go forward in time (colums) to find the cell
reference for the next occurance of "H" and return the date value from
Row 5.

I've tried FIND, MATCH (returns the first occurance and doesn't seem to
work across empty cells) and LOOKUP. My guess is that this will involve
a HLOOKUP and possibly MAX but I am lost.

Any help, pointers or a nice cold beer are appreciated! My project
team is large and we want to make sure we control their movements
well.

Cheers,
Andy


--
farutherford
------------------------------------------------------------------------
farutherford's Profile: http://www.excelforum.com/member.php...o&userid=26663
View this thread: http://www.excelforum.com/showthread...hreadid=399389