finding second largest value

[Arie Bos]

I have the following table:
[TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD]A[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

I use the following formula to find in col A with highest value in col B: =INDEX(A$1:A$5;MATCH(LARGE(B$1:B$5;1);B$1:B$5;0)) Result =C.
Now, to find the second largest (D) I use the same formula, where the argument in the LARGE function is now set from 1 to 2.

Because B3 and B4 are both '5', I guess the formula still returns 'C', not 'D'. Is there a way to return D as the second largest, even if C and D have both 5?

 

[taurean]

One way would be (I used MAX in place of LARGE since it will return the same result as LARGE with second argument 1):
=LOOKUP(MAX($B$1:$B$5),$B$1:$B$5,$A$1:$A$5)

 

[Scott T]

One way would be (I used MAX in place of LARGE since it will return the same result as LARGE with second argument 1):
=LOOKUP(MAX($B$1:$B$5),$B$1:$B$5,$A$1:$A$5)

Lookup requires the data to be in ascending order. The sample given is not in ascending order so the correct value may not be returned depending on the actual data.

 

[Scott T]

This is an array formula and must be committed with CONTROL+SHIFT+ENTER. If done correctly Excel will put {} around the formula. Copy down

=IF(ROWS($A$1:A1)>COUNTIF($B$1:$B$5,MAX($B$1:$B$5)),"",INDEX($A$1:$A$5,SMALL(IF(MAX($B$1:$B$5)=$B$1:$B$5,ROW($B$1:$B$5)-ROW($A$1)+1),ROWS($A$1:A1))))

 

[Arie Bos]

Thank you Scott,
formula is too complicated for me to really understand..
however, when I use it, it shows "C" OK. But how can I have it show D in the next cell?

I would like to create the 'top 3', like
C 5
D 5
B 3

 

[Scott T]

[TABLE="class: grid, width: 256"]
<tbody>[TR]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"]A
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]B
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]C
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]1
[/TD]
[TD="width: 64, bgcolor: transparent"]A
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]2
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]2
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2
[/TD]
[TD="bgcolor: transparent"]B
[/TD]
[TD="bgcolor: transparent, align: right"]3
[/TD]
[TD="bgcolor: transparent, align: right"]3
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]3
[/TD]
[TD="bgcolor: transparent"]C
[/TD]
[TD="bgcolor: transparent, align: right"]5
[/TD]
[TD="bgcolor: transparent, align: right"]5
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]4
[/TD]
[TD="bgcolor: transparent"]D
[/TD]
[TD="bgcolor: transparent, align: right"]5
[/TD]
[TD="bgcolor: transparent, align: right"]5
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]5
[/TD]
[TD="bgcolor: transparent"]E
[/TD]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]6
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]7
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]8
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]C
[/TD]
[TD="bgcolor: transparent"]5
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]9
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]D
[/TD]
[TD="bgcolor: transparent"]5
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]10
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]B
[/TD]
[TD="bgcolor: transparent"]3
[/TD]
[/TR]
</tbody>[/TABLE]


If you can use a helper column in column C then this should work
in C1 and copy down

=B1+(COUNTIF($B$1:$B1,B1)-1)*-0.00001

In B8 and copy down

=INDEX($A$1:$A$5,MATCH(LARGE($C$1:$C$5,ROWS($A$1:A1)),$C$1:$C$5,0))

in C8 and copy down

=VLOOKUP(B8,$A$1:$B$5,2,0)

 

[Aladin Akyurek]

@Arie Bos

[TABLE="class: grid, width: 349"]
<tbody>[TR]
[TD]A[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD]C[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD]D[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD]B[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

In E1 just enter:

=COUNTIFS(B1:B5,">="&LARGE(B1:B5,MIN(3,COUNT(B1:B5))))

In E2 control+shift+enter, not just enter, and copy down:

=IF($F2="","",INDEX($A$1:$A$5,SMALL(IF($B$1:$B$5=$F2,ROW($A$1:$A$5)-ROW($A$1)+1),COUNTIFS($F$2:F2,F2))))

In F2 just enter and copy down:

=IF(ROWS($F$3:F3)>$E$1,"",LARGE($B$1:$B$5,ROWS($F$3:F3)))