index and match formula help

[vinod9111]

Hi All,

got datas in a sheet, need to identify top 5 companies based on value . currently using large function to identify the top 5 value and based on the value using index and match function to throw the corresponding company name. but am facing a small problem, where the value is same the first company name is thrown.

<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=128 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right width=64 height=17 x:num>40</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>mn ltd</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>40</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">mn ltd</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>34</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">xyz ltd</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>30</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">abc ltd</TD></TR></TBODY></TABLE>

formula i have used is given below,

<TABLE style="WIDTH: 399pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=532 border=0 x:str><COLGROUP><COL style="WIDTH: 159pt; mso-width-source: userset; mso-width-alt: 3876" width=212><COL style="WIDTH: 240pt; mso-width-source: userset; mso-width-alt: 5851" width=320><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 159pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=212 height=17 x:num="40">=LARGE($B$21:$B$24,ROW(B1))</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 240pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=320 x:str="mn ltd">=INDEX($A$21:$A$24,MATCH(F21,$B$21:$B$24,0))</TD></TR></TBODY></TABLE>

Thanx in advance for your help.

 

[Aladin Akyurek]

Either run Data|PivotTable or see this example formula system:

http://www.mrexcel.com/forum/showthread.php?t=354211

http://www.mrexcel.com/forum/showthread.php?p=1453214

 

[vinod9111]

Thanx Aladin,

its working, can you tell me why we need to enter , CTRL+SHIFT+ENTER in the name column. i need to know for my understanding. your explanation will be very helpful.

 

[Aladin Akyurek]

Thanx Aladin,

its working, can you tell me why we need to enter , CTRL+SHIFT+ENTER in the name column. i need to know for my understanding. your explanation will be very helpful.

Vinod,

Excel's Help some entries on "array formula". One such entry notes:

"An array formula can perform multiple calculations and then return either a single result or multiple results."