Lookup next item with dates
 

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?

Lookup next item with dates
 

Try this array formula** :

=T(INDEX(B2:B6,MATCH(TRUE,A2:A6=MIN(IF(A2:A6=TODA Y(),A2:A6)),0)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

If there is no date =today you'll get a #N/A error.

--
Biff
Microsoft Excel MVP


"Tom_uk" wrote in message
...
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?

Lookup next item with dates
 

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?

Lookup next item with dates
 

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?

Lookup next item with dates
 

Sorry guys

this now doesnt seem to work - it was fine yesterday!

I have had to edit the formula to take in the other pages it gathers the
information from - i assume this is the problem. I am currently using

=INDEX('Project 1'!D4:D36,MAX(('Project 1'!C4:C36=NOW())*('Project
1'!D4:D36<"")*ROW('Project 1'!C4:D36)))

It will return one correct result if i enter a certain date but any other
date will return "0"

Do you need a better description than that?

"Tom_uk" wrote:

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?