One simple play with a tiebreaker to deliver the desired results
Assume source data within A1:C2
where A1:C1 = sports, A2:C2 = scores
In E3: =IF(A2="","",A2-COLUMN()/10^10)
This is the criteria row, with tiebreaker
In E1:
=INDEX($A1:$C1,MATCH(LARGE($E$3:$G$3,COLUMNS($A:A) ),$E$3:$G$3,0))
Copy down to E2. Select E1:E3, copy across to G3. Min/hide row3. The
descending auto-sort results of the source data will appear in E1:G3. Ties,
if any, will appear in the same relative order that they are within the
source data. Adapt to suit.
Success? Celebrate it, click the YES button below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik
---
"g.king" wrote:
I have a worksheet with names of sports along row 1. In row 2 there are
scores [out of 25] for each sport. To the right of this table I need to find
the sport with the highest score, 2nd, 3rd highest, etc. To do the ranking I
am using =LARGE(A2:J2,1). The final argument is changed to 2 or 3 to achieve
the ranking and works acceptably even if 2 sports have the same score.
My problem is that I also need to know which sport achieved the highest
score. Lets say the cell showing the highest score in the area to the right
is cell M2. I can do this using the Hlookup function if I duplicate the
headings A1:J1 and paste them below the scores [say A3:J3] -
=hlookup(M2,A2:J2,2,false). The problem with this method is that there are
often 2 sports with the same score €“ lets say basketball and hockey both
have a score of 23. For the highest score the function finds 23 in M2 and
returns €śBasketball€ť as the first instance in the table. The 2nd highest
result [in O2] then also finds €śbasketball€ť.
Ive experimented with Cell, Offset and combinations/nested variations of
these with Lookup and Large but to no avail. Is there a better way to achieve
what I want?