finding second largest value

[Arie Bos]

I have the following table:

A	2
B	3
C	5
D	5
E	1

<tbody>
</tbody>

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]

	A	B	C
1	A	2	2
2	B	3	3
3	C	5	5
4	D	5	5
5	E	1	1
6
7
8		C	5
9		D	5
10		B	3

<tbody>
</tbody>

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

A	2			3
B	3			C	5
C	5			D	5
D	5			B	3
E	1

<tbody>
</tbody>

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)))