Index-Match the largest, 2nd largest etc issue

Jasesair

Active Member
Joined
Apr 8, 2015
Messages
282
Office Version
  1. 2016
I am wanting to generate the name (C8:C57) attached to the largest value in HF8:HF57. The below formula is working but when I adjust generate the 2nd largest and 3rd largest, it has problems if the values are duplicates.

Largest =INDEX($C$8:$C$57,MATCH(LARGE($HF$8:$HF$57,1),$HF$8:$HF$57,0))
2nd Largest = INDEX($C$8:$C$57,MATCH(LARGE($HF$8:$HF$57,2),$HF$8:$HF$57,0))
3rd Largest = INDEX($C$8:$C$57,MATCH(LARGE($HF$8:$HF$57,3),$HF$8:$HF$57,0))

As an example, the largest values are 3, 3, and 2. Any help to amend the formulas would be much appreciated.
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Try this:
Book1
ABCDEFGHIJ
1FindWith Max & IFWith MaxIFsWith Max & IFWith MaxIFs
2AAA1212828AAA14AAA14
3AAA2422626AAA12AAA12
4AAA3632222AAA11AAA11
5AAA4842020AAA10AAA10
6AAA510
7AAA612
8AAA714
9AAA816
10AAA918
11AAA1020
12AAA1122
13AAA1226
14AAA1326
15AAA1428
16AAA153
17AAA165
18AAA177
19AAA189
20AAA1911
21AAA2013
22
Sheet1
Cell Formulas
RangeFormula
E2:F2E2=MAX($B$2:$B$21)
E3:E5E3=MAX(IF($B$2:$B$21<E2,$B$2:$B$21))
F3:F5F3=MAXIFS(B2:B21,B2:B21,"<" & F2)
H2:I2H2=INDEX($A$2:$A$21,MATCH(E2,$B$2:$B$21,0))
H3:H5H3=INDEX($A$2:$A$21,MATCH(MAX(IF($B$2:$B$21<E2,$B$2:$B$21)),$B$2:$B$21,0))
I3:I5I3=INDEX($A$2:$A$21,MATCH(MAXIFS($B$2:$B$21,$B$2:$B$21,"<" & F2),$B$2:$B$21,0))
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
Would something like this suffice if adapted to your ranges?

21 02 06.xlsm
CDEF
8Name 12Name 2
9Name 29Name 10
10Name 33Name 4
11Name 48Name 12
12Name 55Name 9
13Name 64Name 7
14Name 76Name 8
15Name 85Name 5
16Name 96Name 6
17Name 108Name 3
18Name 111Name 1
19Name 126Name 11
Match
Cell Formulas
RangeFormula
F8:F19F8=INDEX(C$8:C$19,AGGREGATE(15,6,(ROW(C$8:C$19)-ROW(C$8)+1)/(D$8:D$19=LARGE(D$8:D$19,ROWS(F$8:F8))),COUNTIF(D$8:D$19,">="&LARGE(D$8:D$19,ROWS(F$8:F8)))-ROWS(F$8:F8)+1))
 
Upvote 0
Solution
That's fantastic. Thanks Peter - it works a treat. Anything to avoid array formulas!
 
Upvote 0
You're welcome. (This really is an array formula too, it just doesn't require the C+S+E entry confirmation. :))
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

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