Reporting top 5 values - duplicates causing issues

[Fishboy]

Hi all,

In Excel 2010 I have a table of information containing sales of items. On anther tab within the document I am creating a dashboard with an overview of the main information people want to know. In the 'Sales Sheet' data table I am reading from I am interested in the following columns:

C = Item Name
E = Current Value

I have tried creating a Top 5 table to show which 5 items currently have the highest value and have been using the following formula in cell C1 of my dashboard as indicated below:

= LARGE('Sales Sheet'!$E$5:$E$380,A1)

[TABLE="width: 100, align: center"]
<tbody>[TR]
[TD="align: center"]

A​

[/TD]
[TD="align: center"]

B​

[/TD]
[TD="align: center"]

C​

[/TD]
[/TR]
[TR]
[TD="align: center"]

1​

[/TD]
[TD="align: center"]

ITEM 1​

[/TD]
[TD="align: center"]

600​

[/TD]
[/TR]
[TR]
[TD="align: center"]

2​

[/TD]
[TD="align: center"]

ITEM 1​

[/TD]
[TD="align: center"]

600​

[/TD]
[/TR]
[TR]
[TD="align: center"]

3​

[/TD]
[TD="align: center"]

ITEM 1​

[/TD]
[TD="align: center"]

600​

[/TD]
[/TR]
[TR]
[TD="align: center"]

4​

[/TD]
[TD="align: center"]

ITEM 1​

[/TD]
[TD="align: center"]

600​

[/TD]
[/TR]
[TR]
[TD="align: center"]

5​

[/TD]
[TD="align: center"]

ITEM 2​

[/TD]
[TD]

590​

[/TD]
[/TR]
</tbody>[/TABLE]

The formula checks 'Sales Sheet', looks through E5:E380 for values and returns the highest value (as indicated by the 1 in cell A1). When this is copied down from C1:C5 it looks for the 2nd, 3rd, 4th and 5th highest respectively.

In cell B1 of my dashboard I have the following formula designed to look at the top 5 table then return with the corresponding item name from column C of the 'Sales Sheet' data table:

=INDEX('Sales Sheet'!$C$5:$C$380,MATCH(C1,'Sales Sheet'!$E$5:$E$380,0))

Now, in the 'Sales Sheet' data table I am aware that there are currently 4 items all worth the maximum value of 600, and I have no problem that they are listed in ranks 1-4. My issue is with the INDEX:MATCH formula only returning the same item name for the top 4 spots in the rank table.

How do I amend my formula so that it corresponds to the 2nd, 3rd, 4th and 5th highest values and returns with the correct items instead of the first item it finds over and over again?

 

[Fishboy]

As I have had no replies, if anyone is interested I managed to resolve the issue with the following formula:

=INDEX('Sales Sheet'!$C$5:$C$380,SMALL(IF('Sales Sheet'!$E$5:$E$380=$E17,ROW('Sales Sheet'!$C$5:$C$380)-ROW('Sales Sheet'!$C$5)+1),COUNTIF($E$10:$E17,$E17)))