» Retrieving the Player Who Scored the Highest Number of Points

How can the formula be amended to pick the 2nd and 3rd highest person. I have a list with names, would like to be able to chose and show the score and name of the top 3 people

To select the second largest, use the following command:

=Index(A2:A9,Match(Large(B2:B9,2),B2:B9,0)

To select the third largest, replace the "2" in the Large command by a "3", etc...

Any ideas on how to pick up ties for the first place with up to 5 participants (if say all 5 scored the highest)?

C D E F G
2 max 8
3 cnt 5
4 cnt ent scrs ties
5 - a 7 1 b
6 1 b 8 2 c
7 2 c 8 3 g
8 - d 7 4 i
9 - e 7 5 j
10 - f 7
11 3 g 8
12 - h 7
13 4 i 8
14 5 j 8

the formulas are as follows:

D2: =MAX(E5:E14)
D3: =COUNTIF($E$5:$E$14,"="&D2)
C5: =IF(E5=MAX($E$5:$E$14),COUNTIF($E$5:E5,"="&MAX($E$5:$E$14)),"-")
- drag this until C14
F5: =IF(ISERROR(IF(C5=MIN($D$5:$D$14),C5,SMALL($C$5:$C$14,(ROW(D5)-4)))),"",IF(C5=MIN($D$5:$D$14),C5,SMALL($C$5:$C$14,(ROW(D5)-4))))
- drag this until F14
G5: =IF(ISNA(VLOOKUP(IF(MAX($C$5:$C$14)>=COUNT($E$5:E5),COUNT($E$5:E5),""),$C$5:$E$14,2,0)),"",VLOOKUP(IF(MAX($C$5:$C$14)>=COUNT($E$5:E5),COUNT($E$5:E5),""),$C$5:$E$14,2,0))
- drag this until G14

Note that values in:
D5 to D14: refer to entities
E5 to E14: refer to scores
F5 to F14: refer to the number of entities who tied
G5 to G14: refer to the entities who tied

Hope this helps. ;-)

I have a question, though, how is the Offset Function used?

Thanks.

Thanks for the initial post here. I'd been trying to use the IF function to pick the lowest value in a series of prices from a given row and then look up the supplier at the head of that column. Of course Excel's IF will only process a max of 7 numbers and I have many times that.

Using INDEX, MATCH and MIN it works a treat to pickout the lowest value and then display the suppliers name in row 1. Prices in the following example are in row 12.

=INDEX($G$1:$BK$1,MATCH(MIN(G12:BK12),G12:BK12,0))

Can see from the example in the earlier posts how to pick the 2nd and 3rd largest numbers.

[I]To select the second largest, use the following command:

=Index(A2:A9,Match(Large(B2:B9,2),B2:B9,0)

To select the third largest, replace the "2" in the Large command by a "3", etc...[/I]

What sort of formula or change would you make to my formula to pick out the 2nd and 3rd lowest values?

Thanks in anticipation. This is solving problems I've had in the "too hard" basket for years.

I think I might have solved my own problem :)

Where row 1 lists the suppliers names, row 14 their prices, the following picks
out the name of the 2nd cheapest seller.

=INDEX($I$1:$BM$1,MATCH(SMALL(I14:BN14,2),I14:BN14,0)

To pick out the name of the 3rd cheapest change the "2" in the small function to a "3"

=INDEX($I$1:$BM$1,MATCH(SMALL(I14:BN14,3),I14:BN14,0)

Still to quite understand what the final I14:BN14,0 in each formula does.

But the formula works and thanks to this website and the heading for this post for the initial lead!