Search code examples
excellistmatchworksheet-function

Listing top 5 items from a table in Excel

I am trying to use the LARGE function in Excel, but when I have multiple results with the same number, it is returning the same result twice or more. What I want it to do is move on to the next item in the list. Here is an example:

=INDEX(Movies[Title],MATCH(LARGE(Movies[Rating If Genres],1),Movies[Rating If Genres],0))
=INDEX(Movies[Rating],MATCH(LARGE(Movies[Rating If Genres],1),Movies[Rating If Genres],0))

=INDEX(Movies[Title],MATCH(LARGE(Movies[Rating If Genres],2),Movies[Rating If Genres],0))
=INDEX(Movies[Rating],MATCH(LARGE(Movies[Rating If Genres],2),Movies[Rating If Genres],0))

=INDEX(Movies[Title],MATCH(LARGE(Movies[Rating If Genres],3),Movies[Rating If Genres],0))
=INDEX(Movies[Rating],MATCH(LARGE(Movies[Rating If Genres],3),Movies[Rating If Genres],0))

=INDEX(Movies[Title],MATCH(LARGE(Movies[Rating If Genres],4),Movies[Rating If Genres],0))
=INDEX(Movies[Rating],MATCH(LARGE(Movies[Rating If Genres],4),Movies[Rating If Genres],0))

=INDEX(Movies[Title],MATCH(LARGE(Movies[Rating If Genres],5),Movies[Rating If Genres],0))
=INDEX(Movies[Rating],MATCH(LARGE(Movies[Rating If Genres],5),Movies[Rating If Genres],0))

which yields:

 Title                     | Rating
 --------------------------|---------
 Alien                     | 8.5
 Blade                     | 7.0
>30 Days of Night          | 6.6
>30 Days of Night          | 6.6
 Blade: House of Chthon    | 6.5

This one shows the same movie twice (30 Days of Night), when one of them should be Blade II as it has a 6.6 rating as well.

So what I do is I store the rating in a column in the table if the genres I specify are matched in the genres of the movies (Rating If Genres). After that, I take the highest genres from that list.

How do I make it so it shows the Blade II result from the LARGE function instead of showing the 30 Days of Night twice?

Solution

  • In your "top 5" table, assuming that the first rating (8.5 in your example) is in B2 then try this array formula in A2

    =INDEX(Movies[Title],SMALL(IF(Movies[Rating If Genres]=B2,ROW(Movies[Rating If Genres])-MIN(ROW(Movies[Rating If Genres]))+1),COUNTIF(B$2:B2,B2)))

    confirmed with CTRL+SHIFT+ENTER and copied down

    This will retrieve the correct titles even with repeats in the ratings

    Edit: or perhaps this non-array version......

    =INDEX(Movies[Title],MATCH(1,INDEX((Movies[Rating If Genres]=B2)*(COUNTIF(A$1:A1,Movies[Title])=0),0),0))