LOOKUP function??

[paul....@hotmail.co.uk]

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....@hotmail.co.uk]

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

[Claus Busch]

Hi Paul,

Am Sat, 3 Nov 2012 03:44:07 -0700 (PDT) schrieb
paul....@hotmail.co.uk:

> 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)

[Kevin@Radstock]

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

--
Kevin@Radstock

[paul....@hotmail.co.uk]

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