[[email protected]]

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

[[email protected]]

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

[Claus Busch]

Hi Paul,

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

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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

[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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

[Kevin@Radstock]

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

Originally Posted by

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

[[email protected]]

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