Picking top 5 values when there are duplicate values

Hi,

I am working with a dataset of companies which contains for each company a name and a value for its previous week's revenue. I am trying to create a report which automatically selects the top five revenue companies each week. Unfortunately, because the data is only available up to 2dp, it fairly frequently occurs that two companies have exactly the same revenue and so my list of the top 5 contains duplicates and some companies are missed. For example if Company A had a revenue of 2.34, Company B had a revenue of 1.85, Company C 1.46, Company D 2.98 and Company E 1.85, the list goes Company D, Company A, Company B, Company B, Compancy C.

Currently I am using the formula INDEX($B$19:$C$28,MATCH(LARGE($C$19:$C$27,1),$C$19:$C$28,FALSE),1), where column B is the company name and column C is the previous week's revenue, to pull the largest company, and then changing the number in the large function for the other top 5.

I would be very appreciative if someone could suggest a way to modify this formula to force Excel to rank companies with identical revenues one after the other.

Thank you!

Try this array formula

Formula:

Please Login or Register  to view this content.

---------- Post added at 11:01 AM ---------- Previous post was at 11:00 AM ----------

BTW, you don't need to edit LARGE in this, just copy it down for 5 cells and it auto-updates.

thanks very much! I will try that now.

---------- Post added at 10:08 AM ---------- Previous post was at 10:05 AM ----------

At the moment it is returning a #VALUE error - could this be because of the A1 reference? At the moment I have an unrelated piece of information in cell A1.

---------- Post added at 10:15 AM ---------- Previous post was at 10:08 AM ----------

Actually don't worry - it's just because I didn't hit ctrl shift enter!

ahhhh! thanks!