Pulling info from Column A

Noxqss38242

Board Regular
Joined
Sep 15, 2017
Messages
170
Office Version
  1. 2016
SRT Tracker.xlsm
ABCDEFGHIJKLM
2Assembly(All)
3Component(All)
4Position(All)
5Work(All)
6Under/Over(Multiple Items)
7Terminal(All)
8
9Max of Under/OverColumn Labels
10Row LabelsAbramowicz, ChesterAlexander, CharlesBarney, JimBeard, MichaelBridges, MichaelBuzzell, TommyDarnell, JeffGividen, WilliamGodby, StevenGonzalez, JoseGordon, GaryHall, Joe
11 1.2
12ACCESS0.5
13AERODY0.4
14APU-EL4.10.4
15BODY 5.50.30.60.30.5
16BRKTRA0.61.41.40.24.60.5
17BRKTRL1.60.90.70.61
18CAB 10.30.63.21.8
19CHGSYS1.13.41.1
20COOLSY1.20.21.20.8
21CRNKSY1.90.80.82.343.41.1
22DRVLNE0.9
23ELACCS2.10.30.6
24ENGINE1.60.80.20.6
25EXHSYS0.4
26FRAME 0.80.90.42.21.40.6
27FUELSY1.2
28GAUGE
29HEATER3.53.5
30HVAC 2.70.6
31INFLSY10.40.6
32INTAKE0.7
33LGTSYS0.20.7
34PWRUNT1.188.411.51.93.51.210.18.40.8
35Q COMM0.60.62
Over .2+




I used =Max to pull up largest number in this data range which equaled (11.5)
I also used =large and pulled up the second largest number, etc...

I need to place anywhere in the sheet both the largest, second largest, third largest numbers AND also what those numbers match to in column A
So the largest = PWRUNT
Second largest is also PWRUNT
But let's say it was (3.4) which then should pull "CHGSYS"

I know I can't use VLOOKUP....and I've tried INDEX/MATCH but I'm not skilled in that and it isn't working...
Is this possible?
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,792
Office Version
  1. 365
Platform
  1. Windows
How about
+Fluff v2.xlsm
ABCDEFGHIJKLM
1Assembly(All)ValueCol A
2Component(All)Largest11.5PWRUNT
3Position(All)2nd largest10.1CHGSYS
4Work(All)3rd largest10.1PWRUNT
5Under/Over(Multiple Items)
6Terminal(All)
7
8Max of Under/OverColumn Labels
9Row LabelsAbramowicz, ChesterAlexander, CharlesBarney, JimBeard, MichaelBridges, MichaelBuzzell, TommyDarnell, JeffGividen, WilliamGodby, StevenGonzalez, JoseGordon, GaryHall, Joe
101.2
11ACCESS0.5
12AERODY0.4
13APU-EL4.10.4
14BODY5.50.30.60.30.5
15BRKTRA0.61.41.40.24.60.5
16BRKTRL1.60.90.70.61
17CAB10.30.63.21.8
18CHGSYS10.11.13.41.1
19COOLSY1.20.21.20.8
20CRNKSY1.90.80.82.343.41.1
21DRVLNE0.9
22ELACCS2.10.30.6
23ENGINE1.60.80.20.6
24EXHSYS0.4
25FRAME0.80.90.42.21.40.6
26FUELSY1.2
27GAUGE
28HEATER3.53.5
29HVAC2.70.6
30INFLSY10.40.6
31INTAKE0.7
32LGTSYS0.20.7
33PWRUNT1.188.411.51.93.51.210.18.40.8
34Q COMM0.60.62
Sheet3
Cell Formulas
RangeFormula
D2:D4D2=LARGE($B$10:$M$34,ROWS(D$2:D2))
E2:E4E2=INDEX($A$11:$A$34,AGGREGATE(15,6,(ROW($A$11:$A$34)-ROW($A$11)+1)/($B$11:$M$34=D2),COUNTIFS(D$2:D2,D2)))
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,792
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Forum statistics

Threads
1,147,823
Messages
5,743,406
Members
423,792
Latest member
travisds

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top