I have a huge data base (daily temperatures dating back to 1872), but for simplicity I have limited the values to just a month for this question. What I want to do it list the ten warmest temperatures and then return the date in which it occurred. I am using the following LARGE function to get the ten warmest temperatures (Column B - high temperatures).
=LARGE($B$2:$B$31,$D2)
Column B = High Temp in Data Table
Column D = Rank in Results Table
This works great. However when I use the following Index function to get the date in which it occurred (Column A - Date), it keeps returning the first date in which it occurred when there are multiple occurrences of the same temperature.
=INDEX($A$2:$A$31,MATCH(LARGE($B$2:$B$31,$D2),$B$2:$B$31,0))
Column A = Date in Data Table
Column B = High Temp in Data Table
Column D = Rank in Results Table
In some cases, I have more than 2 occurrences of the same temperatures. For example, the high temperature of 23 degrees occurs 3 times during the month (1/4/2013, 1/11/2013, and 1/12/2013), but only the first one 1/4/2013 shows up in my results table. I would like the other dates to show up. These tables are listed below.
Can anyone help me with this?
Thanks in Advance for any help on this!
Jeff Boyne
Data Table Results Table
Date High Temp Rank High Temp Date
1/2/2013 1 1 27 1/5/2013
1/3/2013 17 2 23 1/4/2013
1/4/2013 23 3 23 1/4/2013
1/5/2013 27 4 23 1/4/2013
1/6/2013 19 5 22 1/13/2013
1/7/2013 18 6 22 1/13/2013
1/8/2013 2 7 19 1/6/2013
1/9/2013 5 8 18 1/7/2013
1/10/2013 8 9 17 1/3/2013
1/11/2013 23 10 17 1/3/2013
1/12/2013 23
1/13/2013 22
1/14/2013 17
1/15/2013 22
1/16/2013 9
1/17/2013 8
1/18/2013 -4
1/19/2013 -7
1/20/2013 -9
1/21/2013 1
1/22/2013 4
1/23/2013 2
1/24/2013 6
1/25/2013 8
1/26/2013 9
1/27/2013 -2
1/28/2013 7
1/29/2013 16
1/30/2013 6
1/31/2013 8
Bookmarks