I NEED HELP PLEASSEEE

adurham22926192

New Member
Joined
Dec 17, 2019
Messages
20
Office Version
2019
Platform
Windows
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
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

J.Ty.

Well-known Member
Joined
Feb 4, 2012
Messages
1,101
Office Version
365, 2013, 2010
Platform
Windows, Web
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
ABC
117712
23548
35371
46756
58893
6121015
741265
865344
Sheet1
Cell Formulas
RangeFormula
C1:C8C1=INDEX($A$1:$A$8,MATCH(LARGE($B$1:$B$8,ROW()),$B$1:$B$8,0))
 

adurham22926192

New Member
Joined
Dec 17, 2019
Messages
20
Office Version
2019
Platform
Windows
Thanks but for some reason that is not working for me it keeps saying #NUM!
 

J.Ty.

Well-known Member
Joined
Feb 4, 2012
Messages
1,101
Office Version
365, 2013, 2010
Platform
Windows, Web
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

New Member
Joined
Dec 17, 2019
Messages
20
Office Version
2019
Platform
Windows
What is n? If i want to start in row 3 what do i put?
 

J.Ty.

Well-known Member
Joined
Feb 4, 2012
Messages
1,101
Office Version
365, 2013, 2010
Platform
Windows, Web
Use ROW()-2 in place of ROW()
 

adurham22926192

New Member
Joined
Dec 17, 2019
Messages
20
Office Version
2019
Platform
Windows
Thanks so much. And how do i do ties?
 

J.Ty.

Well-known Member
Joined
Feb 4, 2012
Messages
1,101
Office Version
365, 2013, 2010
Platform
Windows, Web
It depends on what you want. If you tell me what you expect in such cases, I can try programming it.
 

adurham22926192

New Member
Joined
Dec 17, 2019
Messages
20
Office Version
2019
Platform
Windows
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.

Well-known Member
Joined
Feb 4, 2012
Messages
1,101
Office Version
365, 2013, 2010
Platform
Windows, Web
Here you are. Column C is helper data, you can hide it (right-click on the coulmn header, choose "Hide").
Book2
ABCD
1
2
3177312
435468
553771
667546
788924
81210113
947555
106534865
Sheet1
Cell Formulas
RangeFormula
C3:C10C3=COUNTIF($B$3:$B$10,">"&B3)+COUNTIF(B$3:B3,"="&B3)
D3:D10D3=INDEX($A$3:$A$10,MATCH(ROW()-2,$C$3:$C$10,0))
 

Forum statistics

Threads
1,089,634
Messages
5,409,443
Members
403,263
Latest member
naturally_data

This Week's Hot Topics

Top