troubleshooting Question

Excel 2007 : Lookup multiple duplicate values

Avatar of urec
urecFlag for United Kingdom of Great Britain and Northern Ireland asked on
Microsoft ExcelMicrosoft Applications
3 Comments1 Solution2430 ViewsLast Modified:
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:

        E               F
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....


Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 3 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros