Find Maximum date

I have the followinig three columns

Index DateFrom DateTo
1 28 Apr 04 28 Sep 04
2 28 Sep 04 28 Jan 05
3 28 Sep 04 29 Mar 05
4 29 Mar 05 28 Jul 05
5 28 Jul 05 30 Jun 06

I want to obtain the MAX date in the DateTo column that is nearest to 1 Jan
05 which value resides in cell A1. This value is the result of another
INDEX/MATCH formula. I have used the following formula (array Ctrl+Shift)
obtained from this Discussion Group

={MAX(IF(DateTo<=A1,Index,0))}

The result I get is 28 Sep 04 Index 1, whereas I want 28 Jan 05 with Index 2.

Many thanks to you all
Regards/sgl

=MIN(IF(DateTo>=A1,DateTo))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"sgl" <[email protected]> wrote in message
news:[email protected]...
> I have the followinig three columns
>
> Index DateFrom DateTo
> 1 28 Apr 04 28 Sep 04
> 2 28 Sep 04 28 Jan 05
> 3 28 Sep 04 29 Mar 05
> 4 29 Mar 05 28 Jul 05
> 5 28 Jul 05 30 Jun 06
>
> I want to obtain the MAX date in the DateTo column that is nearest to 1
Jan
> 05 which value resides in cell A1. This value is the result of another
> INDEX/MATCH formula. I have used the following formula (array Ctrl+Shift)
> obtained from this Discussion Group
>
> ={MAX(IF(DateTo<=A1,Index,0))}
>
> The result I get is 28 Sep 04 Index 1, whereas I want 28 Jan 05 with Index
2.
>
> Many thanks to you all
> Regards/sgl
>
>
>
>

See if this is what you're looking for:

With a date in A1 and your sample data in cells A5:C10

B1:
=MAX((((C6:C10<=A1)*(A1-C6:C10))+((C6:C10>A1)*(C6:C10-A1))=MIN(((C6:C10<=A1)*(A1-C6:C10))+((C6:C10>A1)*(C6:C10-A1))))*C6:C10)

Note: Commit that array formula by holding down the [Ctrl][Shift] keys and
press [Enter].

That returns the date that is closest to your target date in A1, regardless
of whether it's over/under.

If you want the row number with that date, use this:
B1:
=MAX((((C6:C10<=A1)*(A1-C6:C10))+((C6:C10>A1)*(C6:C10-A1))=MIN(((C6:C10<=A1)*(A1-C6:C10))+((C6:C10>A1)*(C6:C10-A1))))*ROW(C6:C10))

Commit that formula with [Ctrl][Shift][Enter]


Is that something you can work with?

***********
Regards,
Ron

XL2002, WinXP-Pro


"sgl" wrote:

> I have the followinig three columns
>
> Index DateFrom DateTo
> 1 28 Apr 04 28 Sep 04
> 2 28 Sep 04 28 Jan 05
> 3 28 Sep 04 29 Mar 05
> 4 29 Mar 05 28 Jul 05
> 5 28 Jul 05 30 Jun 06
>
> I want to obtain the MAX date in the DateTo column that is nearest to 1 Jan
> 05 which value resides in cell A1. This value is the result of another
> INDEX/MATCH formula. I have used the following formula (array Ctrl+Shift)
> obtained from this Discussion Group
>
> ={MAX(IF(DateTo<=A1,Index,0))}
>
> The result I get is 28 Sep 04 Index 1, whereas I want 28 Jan 05 with Index 2.
>
> Many thanks to you all
> Regards/sgl
>
>
>
>

Brilliant! all formuals worked a treat and I thank you Ron and Bob for your
assistance and quick response
Regrads/sgl

"sgl" wrote:

> I have the followinig three columns
>
> Index DateFrom DateTo
> 1 28 Apr 04 28 Sep 04
> 2 28 Sep 04 28 Jan 05
> 3 28 Sep 04 29 Mar 05
> 4 29 Mar 05 28 Jul 05
> 5 28 Jul 05 30 Jun 06
>
> I want to obtain the MAX date in the DateTo column that is nearest to 1 Jan
> 05 which value resides in cell A1. This value is the result of another
> INDEX/MATCH formula. I have used the following formula (array Ctrl+Shift)
> obtained from this Discussion Group
>
> ={MAX(IF(DateTo<=A1,Index,0))}
>
> The result I get is 28 Sep 04 Index 1, whereas I want 28 Jan 05 with Index 2.
>
> Many thanks to you all
> Regards/sgl
>
>
>
>