lookup last cell with certain text and give heading of this cell

[Dasha]

Hi all,
I would appreciate help from anybody,
for me huge problem, hopefully with simple solution.
I have table of data for differnt types of absence... in first column are names of different people (so each person has one row of table) and then i have kind of calendar table where each column is one day of the month.. i need to search for S (for sick) and S1/2 (for half day sickness) for each person in whole month and to give me date of last sickness.

I hope its clear enough to understand what i need. Thanx for help.

[DonkeyOte]

It's not clear at this stage if you have other text strings within the table that are to be ignored and, if you do, whether or not they begin with S

In theory - if you don't have other strings then:

=LOOKUP(REPT("Z",255),$C2:$AG2,$C$1:$AG$1)
format as Date

where C2:AG2 holds days info for ee and row 1 the associated dates

If you do have other strings to worry about (but not with S) then you might try:

=LOOKUP(9.99E+307,SEARCH("S",$C2:$AG2),$C$1:$AG$1)
format as Date

If in doubt, post a sample file.

[Marcol]

You would do best to post a sample workbook showing your Sheet Layout and perhaps Before and After examples.

It should clearly illustrate your problem and not contain any sensitive data.

Cheers

[Dasha]

Hope this will help. Always need date of last time cell with SICK appears. Thanxs a lot

[Marcol]

An addition to DonkeyOtes' second formula seems to work

In AM5

=IF(COUNTIF($B5:$AF5,"SICK")=0,"",LOOKUP(9.99E+307,SEARCH("S",$B5:$AF5),$B$4:$AF$4))

I have also simplified your header formulae.

[Dasha]

thanks a lot to everybody. It really helped.