Ranking with Duplicates

[andrewc]

Hi,

I have data arranged as follows:

Column A: Names of Products
Column B: Number of units sold of Product

I would like to create a formula (no helper columns, ideally) to extract from columns A and B the value of the 10 largest numbers in column B and their names from column A. This is easy enough using the LARGE () function on column B to identify the 10 largest values and then INDEX/MATCH to extract the associated names from column A.

The problem arises when there are duplicate values in column B as the INDEX/MATCH formula returns the same name for the duplicate values.

Can someone please suggest a solution?

Thanks!

[Fotis1991]

Hi

1 way could be this.

=LARGE($B$2:$B$1000;ROW(A1))

=INDEX($A$2:$A$628;SMALL(IF($B$2:$B$628=E2;ROW($B$2:$B$628)-ROW($B$2)+1);COUNTIF(E$2:E2;E2)))

[andrewc]

Thank you!

[Fotis1991]

........................