Lookup Max Date

You've already used an Autofilter to isolate the
month of interest. Is the some reason you don't
want to apply a Top 10 filter on Temp to return the
Top 1 Items?

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,35686,12;9,35693,12;9,35700,3;10,35707,4;10,35714,5;10,35721,4;10,35728,5;11,35735,6;11,35742,3;11,35749,7;11,35756,4;11,35763,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))

Note 1. In order to array-enter a formula, you need to hit CONTROL+SHIFT+ENTER at the same time (not just ENTER).

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 2. It should be clear by now that you can list all month numbers in E from E2 on and apply/copy the above formulas for all of them.

Note 3. The above system of formulas can also be adapted for use in a different worksheet than the sheet in which you have your data.

Note 4. These formulas, suitably adapted, can also give you min values for any month.

Note 5. You probably noticed that I didn't use column A that houses the month numbers. For the above computations that column is superfluous.

Aladin

===========

Hi Mark and Aladin,

Thanks Aladin for your response, I'll try that later on. Sorry for the confusion between Temps and Prices - I've got 2 projects on the go, one a database of weather stats, the other a "weekly pricewatch" and they are ending up very similar.

Mark - yep I realise I can get the High and Low values thru filtering, but I'm after the Highest and Lowest values, together with corresponding dates, to be return in cells at the bottom of the list. These values and dates are then refered to by other sheets.

I've been trying to nut this one out for ages now.

Thks Riki

Yep. The system of the formulas I described applies to both projects.

Hi Aladin!

You're a gem! Your solution does extactly what I want. Thanks very much!

Cheers, Riki