<!--[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
<tbody>
</tbody>
The sheet “Stats” is as follows:
A B C
<tbody>
</tbody> <!--[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]-->
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
| Territory: | 01 | | |
| Product | % Increase | | |
| A76.013 | 50 | | |
| X26 | 38 | | |
| X26.00X | 33 | | |
| X26.00X | 33 | | Should be product A67 |
| AX9 | 7 | | |
| | | | |
<tbody>
</tbody>
The sheet “Stats” is as follows:
A B C
| Territory | Product | % Diff | |
| 01 | X3X | 0 | |
| 01 | X39 | 0 | |
| 01 | A76.061 | 5 | |
| 01 | AX9 | 7 | 5th |
| 01 | X26.00X | 33 | 4th |
| 01 | A67 | 33 | 3rd |
| 01 | X26 | 38 | 2nd |
| 01 | A76.013 | 50 | 1st |
| 02 | X26.00X | 0 | |
| 02 | X28 | 17 | |
| 02 | A76.00X | 20 | |
| 02 | 6XX | 20 | |
| 02 | X2A | 25 | |
| 02 | A73 | 80 | |
| 02 | A76.061 | 167 | |
| 02 | X26.006 | 1,595 | |
| 03 | AX9 | 14 | |
| 03 | A68 | 40 | |
| 03 | A82 | 50 | |
| 03 | 1X79 | 80 | |
| 03 | X17 | 100 | |
| 03 | A7X | 508 | |
<tbody>
</tbody>
