Highest, Second Highest , Third Highest and so on

[Guest]

Hi,

Can someone help me with the following please ?

Example:
A B C D E F
G H I J K L
1 Chairs 100 Desk 50 Perfume 25
2 Tables 75 AC 75 Computer 25
3 Stapler 50 Doors 50 Bench 50
4 Telephone 25 Pipes 75 Fan 100
5 Mobile 0 Frame 100 Disk 100

I need to find out the highest, second highest and third highest from the
above ( A1 : F1, A2:F2 , A3: F3 and so on.....) display in G H I J K L.

The solution example for the above should be:
G H I J K L
1 Chairs 100 Desk 50 Perfume 25
2 Tables 75 AC 75 Computer 25
3 Stapler 50 Doors 50 Bench 50
4 Fan 100 Pipes 75 Telephone 25
5 Disk 100 Frame 100 Mobile 0

( Please notice the change in G4: L4 & G5:L5)

Hope I you can understand the above lengthy stuff.....

Thanks & Regards

gkb

 

[Bob Phillips]

G1: =INDEX($A$1:$E$1,MATCH(H1,$B$1:$F$1,0))
H1: = LARGE($A$1:$F$1,1)
I1: =INDEX($A$1:$E$1,MATCH(J1,$B$1:$F$1,0))
J1: = LARGE($A$1:$F$1,2)
K1: =INDEX($A$1:$E$1,MATCH(L1,$B$1:$F$1,0))
L1: = LARGE($A$1:$F$1,3)

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

 

[Guest]

Hi Bob,

Seems this is working. I am still applying this formula into a large
worksheet.

But before that I thought I should not miss say Thank you.

Regards

gkb

 

[bplumhoff]

Hi Bob,

Unfortunately LARGE does not work if there are identical values. In the
third row you get STAPLES three times...

I suggest to enter into G1:O1
=RANK($B1,$B1:$F1) =RANK($D1,$B1:$F1)+COUNTIF($B1,$D1) =RANK($F1,$B1:$F1)+COUNTIF($B1:$D1,$F1) =INDEX($A1:$F1,MATCH(1,$G1:$I1,)*2-1) =INDEX($A1:$F1,MATCH(1,$G1:$I1,)*2) =INDEX($A1:$F1,MATCH(2,$G1:$I1,)*2-1) =INDEX($A1:$F1,MATCH(2,$G1:$I1,)*2) =INDEX($A1:$F1,MATCH(3,$G1:$I1,)*2-1) =INDEX($A1:$F1,MATCH(3,$G1:$I1,)*2)

then copy down as far as required.

Regards,
Bernd