I looking for a function that will return the value that is in one of
5 cells that is matches the closest to Todays date. Thus -

I have 5 date values in cells A5 to E5
Todays date is in A1
In A10 I want to return the value of one of A5-E5 that is closest to
todays date that is in A1

"Seanie" wrote:
I looking for a function that will return the value
that is in one of 5 cells that is matches the closest
to Todays date. Thus -
I have 5 date values in cells A5 to E5
Todays date is in A1
In A10 I want to return the value of one of A5-E5 that
is closest to todays date that is in A1

Depends on what you mean by "closest". Perhaps:

=LOOKUP(A1,A5:E5)

formatted as Date. But that returns the closest date that is the same or
earlier. So if B5 contains 3/1/2012 (March 1), C5 contains 4/1/2012 and A1
contains 3/31/2012, LOOKUP will return 3/1/2012, not 4/1/2012. Okay?

Note: LOOKUP requires that A5:E5 be in ascending order. Okay?

On Monday, June 25, 2012 4:14:27 PM UTC+1, joeu2004 wrote:
"Seanie" wrote:
I looking for a function that will return the value
that is in one of 5 cells that is matches the closest
to Todays date. Thus -
I have 5 date values in cells A5 to E5
Todays date is in A1
In A10 I want to return the value of one of A5-E5 that
is closest to todays date that is in A1

Depends on what you mean by "closest". Perhaps:

=LOOKUP(A1,A5:E5)

formatted as Date. But that returns the closest date that is the same or
earlier. So if B5 contains 3/1/2012 (March 1), C5 contains 4/1/2012 and A1
contains 3/31/2012, LOOKUP will return 3/1/2012, not 4/1/2012. Okay?

Note: LOOKUP requires that A5:E5 be in ascending order. Okay?

Thanks the 'same or earlier' doesn't really work for me, as the closest might be greater than todays date

Hi Sean,

Am Mon, 25 Jun 2012 10:55:06 -0700 (PDT) schrieb :

Thanks the 'same or earlier' doesn't really work for me, as the closest might be greater than todays date

try:
=MIN(IF(ABS(A5:E5-$A$1)=MIN(ABS(A5:E5-$A$1)),A5:E5))
and enter the formula with CTRL+Shift+Enter


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

On Monday, June 25, 2012 7:14:39 PM UTC+1, Claus Busch wrote:
Hi Sean,

Am Mon, 25 Jun 2012 10:55:06 -0700 (PDT) schrieb :

Thanks the 'same or earlier' doesn't really work for me, as the closest might be greater than todays date

try:
=MIN(IF(ABS(A5:E5-$A$1)=MIN(ABS(A5:E5-$A$1)),A5:E5))
and enter the formula with CTRL+Shift+Enter


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Bingo! that seems to have worked great, Thanks