Lookup Date of Max Temp

Hi all - This formula suggested by Aladin, with cell references changed by me, is soooo frustratingly close to solving all my problems!

This formula selects and displays the range address of months from a list of 365 days, labels are B1:Date and C1:Temps

=ADDRESS(MATCH(E2, MONTH($B$1:$B$20), 0), COLUMN(C1)) & ":" & ADDRESS(MAX((MONTH($B$2:$B$20)=E2) * ROW($C$2:$C$20)), COLUMN(C1))

e.g. if I enter "3" for March in cell F2, it returns C62:C92 - the correct range of March days.
BUT - when I enter "1" for January, it returns C2:C62 which is the range for both Jan and Feb. You'd expect it to return C2:C32 for Jan. All other months work fine, just January seems to return 60 days.

Aladin, or anyone else - can you offer a solution? As I said, this is so very close to a final solution to my problem.

"Desperate and dateless" - until I sort this out at least!

Cheers, Riki

======================
Posted by Aladin Akyurek on August 22, 2001 at 10:26:05:

In Reply to: Lookup Max Date posted by Riki Phelan on August
21, 2001 at 22:55:52:

Riki,

Although using filtering is a good idea, one drawback is
that it's not fully automatic. The system of formulas that
follow, while a bit complicated, does not have this
drawback. Moreover, as I understood from your initial post
(you were talking about prices on Fridays of every month,
not about temperatures), you wanted to produce min's and
max's with respect to each month. The formulas below would
allow you to do that too.

Lets start with some data. The following sample

{"month", "date", "price"; 8,35665,1; 8,35672,2; 9,35679,4; 9,3568
6,12; 9,35693,12; 9,35700,3; 10,35707,4; 10,35714,5; 10,35721,4; 1
0,35728,5; 11,35735,6; 11,35742,3; 11,35749,7; 11,35756,4; 11,357
63,2; 12,35770,4; 12,35777,1; 12,35784,5; 12,35791,2}

occupies the range A1:C20. The numbers like 35672 are just
dates and show up here as they are internally stored in
Excel, so don't worry about them.

In E2 enter: 9 [ month (number) of interest
In F2 array-enter:
=ADDRESS(MATCH(E2, MONTH($B$1:$B$20),0), COLUMN(C1)) & ":" & ADDRESS(MAX((MONTH($B$2:$B$20)=E2) * ROW($C$2:$C$20)), COLUMN(C1))
In G2 array-enter:
=INDEX($B$1:$B$20, MAX((INDIRECT(F2) = MAX(INDIRECT(F2))) * ROW(INDIRECT(F2))))
This gives you the date on which the price/temp is the
highest in month 9.
In H2 enter: =MAX(INDIRECT(F2))
This gives you the highest price/temp in month 9.
Note: You probably noticed that I didn't use column A that
houses the month numbers. For the above computations that
column is superfluous.

Aladin

======================
as:
from my filtered list?
=SUBTOTAL(1, INDEX(B4:B369, MATCH(C375,C4:C369,0),1)) but only
if the temp is unique in the year. It returns an error if I
display all of July. This is becuase I have 30.0 on Jan 10,
and 30.0 on Jul 10.
returned from a filtered list? I'm at a loss...