I record weather data in several Excel spreadsheets. One of the Worksheets holds a list of daily maximum temperatures with the corresponding numeric date alongside:
2 0.6 40179
3 1.3 40180
etc down to row 366. E2:F366 has been assigned the name MaxTemp.
I am looking to extract the top (or bottom) 10 values from this list (10 Warmest Days, 10 Coldest Days) with the corresponding date.
The following works quite well...
CJ CK CM
3 1 =LARGE(MaxTemp),CJ3 26.1 =VLOOKUP($CK3,E2:F366,2,FALSE) 17 Jun 2010
4 2 =LARGE(MaxTemp),CJ4 25.3 =VLOOKUP($CK4,E2:F366,2,FALSE) 24 Jul 2010
repeated for each of the 10 entries returned.
The problem is some of the values returned in colum CK are duplicates - this is possible as different days can reach the same maximum temperature.
Unfortunately VLOOPKUP only returns the first occurence of a duplicate value. So, when two values are extracted which are the same the corresponding date is the same date (the first one) when in fact the second occurrence of the duplicate actually has a date later in the list.
Ideally, VLOOKUP would have an occurrence parameter, i.e. give the the 2 or 3 occurence of this value I am searching with - but it does not have such a parameter.
Can anyone offer a solution to my problem....