LOOKUP function??

[paul.mullan]

Hi Guys -

I have a simple dataset that counts illness occurance in a wee.
Something like this:

A B C D E
1 1/10/12 2/10/12 3/10/12 4/10/12 5/10/12
2 Mon Tues Wed Thurs Fri
3 ill ill

I am trying to find a function that will return the date of the last
occurance of illness in this instance 3/10/12.

Any help would be massively appreciated

Many thanks

 

[paul.mullan]

...... that was supposed top read "illness in a week"!!!

 

[Claus Busch]

Hi Paul,

Am Sat, 3 Nov 2012 03:44:07 -0700 (PDT) schrieb
(e-mail address removed):

A B C D E
1 1/10/12 2/10/12 3/10/12 4/10/12 5/10/12
2 Mon Tues Wed Thurs Fri
3 ill ill

I am trying to find a function that will return the date of the last
occurance of illness in this instance 3/10/12.

try:
=INDEX(1:1,LOOKUP(2,1/(3:3<>""),COLUMN(1:1)))
or
=INDEX(1:1,MATCH("",3:3,-1))


Regards
Claus Busch

 

[Claus Busch]

Hi Paul,

Am Sat, 3 Nov 2012 12:42:02 +0100 schrieb Claus Busch:

=INDEX(1:1,LOOKUP(2,1/(3:3<>""),COLUMN(1:1)))
or
=INDEX(1:1,MATCH("",3:3,-1))

or
=LOOKUP(2,1/(3:3="ill"),1:1)


Regards
Claus Busch

 

[Kevin@Radstock]

Hi Ensuring your dates are in ascending order
=LOOKUP(2^20,SEARCH("Ill",A3:E3),A1:E1)

 

[paul.mullan]

Thanks all for your input. All solutions worked brilliantly.

Kevin @ Radstock out of interest wat was the 2^20 part of your function getting excel to look for?

Thanks again