Index/Match/Large Function - multiples of the same number affecting results

[Jzfbkr]

i'm using the following function:

=INDEX(A432:A437,MATCH(LARGE(B432:B437,1),B432:B437,0)))

on the below data set

	a	b
432	Item list	Frequency
433	Soup	1
434	Chocolate	2
435	Bananas	3
436	Crisps	3
437	Steak	4

<tbody>
</tbody>

<tbody>
</tbody>

I want to return the Item based on the Largest, 2nd Largest, 3rd Largest result, 4th Largest result & 5th Largest result.

But with duplicate frequencies e.g 3

The item will only return as the top listed 'Bananas'

How can I solve this?

Thanks

 

[Domenic]

For simplicity, let's assume that A2:B10 contains the data. Try the following...

D2: 5

(Enter 5 in cell D2, which indicates that you want a Top 5 list. This can be changed as desired. So, for example, if you enter 3 instead, you'll get a Top 3 list.)

E2:

=COUNTIF(B2:B10,">="&LARGE(B2:B10,D2))

F2, copied down:

=IF(ROWS(F$2:F2)<=$E$2,LARGE($B$2:$B$10,ROWS(F$2:F2)),"")

G2, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=IF(N(F2),INDEX($A$2:$A$10,SMALL(IF($B$2:$B$10=F2,ROW($A$2:$A$10)-ROW($A$2)+1),COUNTIF($F$2:F2,F2))),"")

Adjust the ranges, accordingly.

Hope this helps!

 

[Jzfbkr]

Fantastic!! Thanks for your help saved me alot of time.

Really well explained.

Thanks again Domenic

I dont suppose there is a way to have a secondary layer? If not no problem you've already helped enough

 

[eddiec4367]

For simplicity, let's assume that A2:B10 contains the data. Try the following...

D2: 5

(Enter 5 in cell D2, which indicates that you want a Top 5 list. This can be changed as desired. So, for example, if you enter 3 instead, you'll get a Top 3 list.)

E2:

=COUNTIF(B2:B10,">="&LARGE(B2:B10,D2))

F2, copied down:

=IF(ROWS(F$2:F2)<=$E$2,LARGE($B$2:$B$10,ROWS(F$2:F2)),"")

G2, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=IF(N(F2),INDEX($A$2:$A$10,SMALL(IF($B$2:$B$10=F2,ROW($A$2:$A$10)-ROW($A$2)+1),COUNTIF($F$2:F2,F2))),"")

Adjust the ranges, accordingly.

Hope this helps!

I've implemented this into a recent spreadsheet which sometimes returns results of 0. Using the example above if say chocolate had a frequency of 0 my results would not show chocolate at all. Is there a way to change that so it shows chocolate with a frequency of 0?