Shane,

Sorry i just typed in random dates as an example - i am using calendar dates.

The below formula you give did the job perfectly though
Thanks!


"Shane Devenshire" wrote:

I don't understand your dates? I'm ok up to 31/04/09 - then I lost?

The answer also depends on whether you want a blank action returned or not.
If you had real dates in first column then if you want a blank until today is
21/05/09 you can use

=INDEX(B1:B5,MATCH(TODAY(),A1:A5,-1))

The answer also depends on how you want to handle the situation where today
is one of the dates on the list, do you show the prompt for that day or the
one for the next date?

The following array formula handles avoiding the display of blank actions:

=INDEX(B1:B5,MAX((A1:A5=TODAY())*(B1:B5<"")*ROW( 1:5)))

but it shows the prompt for the current date when that date is on the list.
If you don't want to see today's prompt, but in fact the next one change the
formula to read:

=INDEX(B1:B5,MAX((A1:A5=NOW())*(B1:B5<"")*ROW(1: 5)))

this is also an array - press Shift+Ctrl+Enter to enter it rather than Enter.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Tom_uk" wrote:

Hi all,

I cant seem to get this to work properly.

(see below)
I have a list of dates, firstly the 5 previous days then the weeks previous
to that. Next to some of these dates will be an action - this can change
depending on the project. The idea is to have a prompter tell me what the
next action is due depending on the day eg. last friday i completed C, now
its monday and D is due this coming thursday.

Everytime i do a VLOOKUP all i get is the first action despite what date i
reference to!

current date: TODAY() eg. 10/05/09

A B
1 28/05/09 security passes due
2 21/05/09
3 14/05/09 email guest list
4 07/05/09 send out rsvp
5 31/04/09 finalise colourscheme

next action: "email guest list"

Is this possible?