Auto generate top 5 league table

**phillipmcmahon** · 04-08-2008, 12:16 PM

Hi everyone, I wondered if you could please help me.

I have ranked data sorted alphabetically and need to generate a new table containing the top 5 ranked values which updates if the rankings change. I looked at VLOOKUP but this doesn't seem to help when I have rankings of the same value. I can't think of any other solution. I have provided some ras data.

I doubt there is an easy solution without writing a macro but I am hoping I am wrong.

Thanks,

Phill

**NBVC** · 04-08-2008, 12:31 PM

See attached:

Formula in F3:

Please Login or Register  to view this content.

confirmed with CTRL+SHIFT+ENTER and copied down.

Formula in G3:

Please Login or Register  to view this content.

copied down

**phillipmcmahon** · 04-08-2008, 12:40 PM

Just too awesome. Thanks so much for the quick reply.

I am guessing that to change to use for low rankings to high rankings I just change LARGE to SMALL in the ranking column. It seemed to work fine.

Again, thanks so much. Now I just have to figure out how it works...

Phill

**NBVC** · 04-08-2008, 12:45 PM

I am guessing that to change to use for low rankings to high rankings I just change LARGE to SMALL in the ranking column.

if you mean you want it listed so that of the top 5, you see them in ascending order, then change the formula to:

=LARGE($C$3:$C$18,6-ROWS($A$1:$A1)) copied down

I just had a very recent and very similar question and I added a brief explanation... See here: http://www.excelforum.com/showthread.php?t=640837

**cadujunior** · 03-15-2011, 09:40 AM

Hi guys, I did like this post, but just to complicate it.. what if I wanted to make the top5 with those teams splitting them by state/region? As an example... split those 16 lines in 4 regions and them select the region to know the top5 inside that region? How would that work?

thanks,
Carlos

**NBVC** · 03-15-2011, 03:40 PM

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.