Looking up a value next to a designated cell

[g.king]

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 – let’s 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â€.

I’ve 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?

 

[Max]

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]

Sorry, I've been away for a few days. This helps enormously.
Many thanks!
--
G.King

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
---

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 – let’s 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â€.

I’ve 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?

 

[Max]

Sorry, I've been away for a few days. This helps enormously.
Many thanks!

That's good. You're welcome. If you can, do spare a moment to click the YES
button (like the one below) in that response.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik
---