Yet another INDEX & MATCH question

[ITDavid]

<!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--> I am trying to list the top 5 performing products, listing the actual order is not a problem. My problem is when I show the actual product against the results (% increase). If all the results are unique then there isn’t a problem, but my formula repeats the product number when the value is not unique. The issue is complicated by referencing another sheet in the same workbook and having a territory selection field (ie. only reference territory selected in B2).


My formula for each of the 5 lines is:
{=INDEX(Stats!$B$2:$B$1880,MATCH($B3,Stats!$C$2:$C$499,0))}

My sheet is as follows:

A B
[TABLE="class: MsoNormalTable, width: 258"]
<tbody>[TR]
[TD="width: 102"]

Territory:​

[/TD]
[TD="width: 77"]

01​

[/TD]
[TD="width: 20"]
[/TD]
[TD="width: 145"]
[/TD]
[/TR]
[TR]
[TD="width: 102"] Product
[/TD]
[TD="width: 77"]

% Increase​

[/TD]
[TD="width: 20"]
[/TD]
[TD="width: 145"]
[/TD]
[/TR]
[TR]
[TD="width: 102"] A76.013
[/TD]
[TD="width: 77"]

50​

[/TD]
[TD="width: 20"]
[/TD]
[TD="width: 145"]
[/TD]
[/TR]
[TR]
[TD="width: 102"] X26
[/TD]
[TD="width: 77"]

38​

[/TD]
[TD="width: 20"]
[/TD]
[TD="width: 145"]
[/TD]
[/TR]
[TR]
[TD="width: 102"] X26.00X
[/TD]
[TD="width: 77"]

33​

[/TD]
[TD="width: 20"]
[/TD]
[TD="width: 145"]
[/TD]
[/TR]
[TR]
[TD="width: 102"] X26.00X
[/TD]
[TD="width: 77"]

33​

[/TD]
[TD="width: 20"]
[/TD]
[TD="width: 145"] Should be product A67
[/TD]
[/TR]
[TR]
[TD="width: 102"] AX9
[/TD]
[TD="width: 77"]

7​

[/TD]
[TD="width: 20"]
[/TD]
[TD="width: 145"]
[/TD]
[/TR]
[TR]
[TD="width: 102"]
[/TD]
[TD="width: 77"]

​

[/TD]
[TD="width: 20"]
[/TD]
[TD="width: 145"]
[/TD]
[/TR]
</tbody>[/TABLE]


The sheet “Stats” is as follows:
A B C
[TABLE="class: MsoNormalTable, width: 190"]
<tbody>[TR]
[TD="width: 63"]

Territory​

[/TD]
[TD="width: 63"] Product
[/TD]
[TD="width: 64"]

% Diff​

[/TD]
[TD="width: 64"]
[/TD]
[/TR]
[TR]
[TD="width: 63"]

01​

[/TD]
[TD="width: 63"] X3X
[/TD]
[TD="width: 64"]

0 ​

[/TD]
[TD="width: 64"]
[/TD]
[/TR]
[TR]
[TD="width: 63"]

01​

[/TD]
[TD="width: 63"] X39
[/TD]
[TD="width: 64"]

0 ​

[/TD]
[TD="width: 64"]
[/TD]
[/TR]
[TR]
[TD="width: 63"]

01​

[/TD]
[TD="width: 63"] A76.061
[/TD]
[TD="width: 64"]

5 ​

[/TD]
[TD="width: 64"]
[/TD]
[/TR]
[TR]
[TD="width: 63"]

01​

[/TD]
[TD="width: 63"] AX9
[/TD]
[TD="width: 64"]

7 ​

[/TD]
[TD="width: 64"] 5th
[/TD]
[/TR]
[TR]
[TD="width: 63"]

01​

[/TD]
[TD="width: 63"] X26.00X
[/TD]
[TD="width: 64"]

33 ​

[/TD]
[TD="width: 64"] 4th
[/TD]
[/TR]
[TR]
[TD="width: 63"]

01​

[/TD]
[TD="width: 63"] A67
[/TD]
[TD="width: 64"]

33 ​

[/TD]
[TD="width: 64"] 3rd
[/TD]
[/TR]
[TR]
[TD="width: 63"]

01​

[/TD]
[TD="width: 63"] X26
[/TD]
[TD="width: 64"]

38 ​

[/TD]
[TD="width: 64"] 2nd
[/TD]
[/TR]
[TR]
[TD="width: 63"]

01​

[/TD]
[TD="width: 63"] A76.013
[/TD]
[TD="width: 64"]

50 ​

[/TD]
[TD="width: 64"] 1st
[/TD]
[/TR]
[TR]
[TD="width: 63"]

02​

[/TD]
[TD="width: 63"] X26.00X
[/TD]
[TD="width: 64"]

0 ​

[/TD]
[TD="width: 64"]
[/TD]
[/TR]
[TR]
[TD="width: 63"]

02​

[/TD]
[TD="width: 63"] X28
[/TD]
[TD="width: 64"]

17 ​

[/TD]
[TD="width: 64"]
[/TD]
[/TR]
[TR]
[TD="width: 63"]

02​

[/TD]
[TD="width: 63"] A76.00X
[/TD]
[TD="width: 64"]

20 ​

[/TD]
[TD="width: 64"]
[/TD]
[/TR]
[TR]
[TD="width: 63"]

02​

[/TD]
[TD="width: 63"] 6XX
[/TD]
[TD="width: 64"]

20 ​

[/TD]
[TD="width: 64"]
[/TD]
[/TR]
[TR]
[TD="width: 63"]

02​

[/TD]
[TD="width: 63"] X2A
[/TD]
[TD="width: 64"]

25 ​

[/TD]
[TD="width: 64"]
[/TD]
[/TR]
[TR]
[TD="width: 63"]

02​

[/TD]
[TD="width: 63"] A73
[/TD]
[TD="width: 64"]

80 ​

[/TD]
[TD="width: 64"]
[/TD]
[/TR]
[TR]
[TD="width: 63"]

02​

[/TD]
[TD="width: 63"] A76.061
[/TD]
[TD="width: 64"]

167 ​

[/TD]
[TD="width: 64"]
[/TD]
[/TR]
[TR]
[TD="width: 63"]

02​

[/TD]
[TD="width: 63"] X26.006
[/TD]
[TD="width: 64"]

1,595 ​

[/TD]
[TD="width: 64"]
[/TD]
[/TR]
[TR]
[TD="width: 63"]

03​

[/TD]
[TD="width: 63"] AX9
[/TD]
[TD="width: 64"]

14 ​

[/TD]
[TD="width: 64"]
[/TD]
[/TR]
[TR]
[TD="width: 63"]

03​

[/TD]
[TD="width: 63"] A68
[/TD]
[TD="width: 64"]

40 ​

[/TD]
[TD="width: 64"]
[/TD]
[/TR]
[TR]
[TD="width: 63"]

03​

[/TD]
[TD="width: 63"] A82
[/TD]
[TD="width: 64"]

50 ​

[/TD]
[TD="width: 64"]
[/TD]
[/TR]
[TR]
[TD="width: 63"]

03​

[/TD]
[TD="width: 63"] 1X79
[/TD]
[TD="width: 64"]

80 ​

[/TD]
[TD="width: 64"]
[/TD]
[/TR]
[TR]
[TD="width: 63"]

03​

[/TD]
[TD="width: 63"] X17
[/TD]
[TD="width: 64"]

100 ​

[/TD]
[TD="width: 64"]
[/TD]
[/TR]
[TR]
[TD="width: 63"]

03​

[/TD]
[TD="width: 63"] A7X
[/TD]
[TD="width: 64"]

508 ​

[/TD]
[TD="width: 64"]
[/TD]
[/TR]
</tbody>[/TABLE]
<!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} </style> <![endif]-->

 

[sanrv1f]

try this

B3

=LARGE(IF(stats!$A$2:$A$23=B$1,stats!$C$2:$C$23),ROWS(B$3:B3))

A3

=INDEX(stats!$B$2:$B$23,LARGE(IF(stats!$A$2:$A$23=$B$1,IF(stats!$C$2:$C$23=B3,ROW(stats!$C$2:$C$23)-ROW(stats!$C$2)+1)),COUNTIFS(B$3:B3,B3)))

both confirmed with Ctrl+Shift+Enter and drag down

 

[ITDavid]

Thanks Sankar

It works a treat, you are a clever person