I NEED HELP PLEASSEEE

[adurham22926192]

Hi I have the competitor numbers in column A (A1: 101, A2:108 etc) and their score in column B (B1: 87.00, B2: 84.00) and i need a formula for C1 to look up the highest score in the column B and return the competitors number, and so on so look up the second largest score and return the competitors number.

Please help thanks so much

 

[J.Ty.]

Here it is. At the moment it assumes there are no ties. Please let me know if they are pssible and what you expect then.

Book2
	A	B	C
1	1	77	12
2	3	54	8
3	5	37	1
4	6	75	6
5	8	89	3
6	12	101	5
7	4	12	65
8	65	34	4
Sheet1

Cell Formulas
Range	Formula
C1:C8	C1	=INDEX($A$1:$A$8,MATCH(LARGE($B$1:$B$8,ROW()),$B$1:$B$8,0))

 

[adurham22926192]

Thanks but for some reason that is not working for me it keeps saying #NUM!

 

[J.Ty.]

My formulas are designed to start in row 1. If you are using them elsewhere, you should adjust them by replacing ROW() with (ROW()-n+1), if you are starting in row n.

 

[adurham22926192]

What is n? If i want to start in row 3 what do i put?

 

[J.Ty.]

Use ROW()-2 in place of ROW()

 

[adurham22926192]

Thanks so much. And how do i do ties?

 

[J.Ty.]

It depends on what you want. If you tell me what you expect in such cases, I can try programming it.

 

[adurham22926192]

So say competitior no. 1 got 100 points and competitor 2 and 3 got 90 points each i want the formula to say both the numbers not just 102 twice

 

[J.Ty.]

Here you are. Column C is helper data, you can hide it (right-click on the coulmn header, choose "Hide").

Book2
	A	B	C	D
1
2
3	1	77	3	12
4	3	54	6	8
5	5	37	7	1
6	6	75	4	6
7	8	89	2	4
8	12	101	1	3
9	4	75	5	5
10	65	34	8	65
Sheet1

Cell Formulas
Range	Formula
C3:C10	C3	=COUNTIF($B$3:$B$10,">"&B3)+COUNTIF(B$3:B3,"="&B3)
D3:D10	D3	=INDEX($A$3:$A$10,MATCH(ROW()-2,$C$3:$C$10,0))