Rank/Vlookup? Question

[excelquestion7899]

I'm not great with Excel, so any help would be awesome!

I'm taking stats for my team, and I'm trying to create Top 5 lists for different stats. For example,

Player Goals
Al 4
Bob 11
Craig 1
Dave 7
Ed 5
Frank 3
Gary 9

I'd like to get a top 5 list, so it would rank

1. Bob
2. Gary
3. Dave
etc.

I can use =rank to give each player a number rank, but I don't know how to change that to a name.

Thanks!

[hoyasaxa215]

Can't you just sort the columns from highest to lowest?

[excelquestion7899]

I have multiple stats on my spreadsheets, like goals/assists/playing time, etc. I can sort by one column, but I'd have to redo it for each one.

In addition, I'm hoping to be able to eventually make printouts where top 5's for every stat is on the same sheet.

[Speshul]

Sorting as hoyasaxa215 suggested, or a Pivot table, would work much better I think.... This is probably overkill.

Make the BOLD RED reference in this formula point to the rank number you are looking for (a cell that contains the number 1 for example would be rank 1)
Make the BOLD BLUE number the total number of player names who are ranked
Make the BOLD GREEN areas the range which contains the number of goals scored
Make the BOLD BLACK area the list of player names

Hold Control and Shift when you press Enter to input this formula properly. Start at Rank 1, and copy/drag the formula down.

=INDEX($A$1:$A$7,SUM(ROW($1:$7)*(--($B$1:$B$7=LARGE($B$1:$B$7,A14)))))


Alternately, if you don't want to have to put a 1, 2, 3 in the bold red, replace the bold red with ROW(1:1)
=INDEX($A$1:$A$7,SUM(ROW($1:$7)*(--($B$1:$B$7=LARGE($B$1:$B$7,ROW(1:1))))))

[excelquestion7899]

Thank you, this is what I was looking for!

[Speshul]

Thanks for the feedback.

If you could, please go to the Top of your first post and look under Thread Tools for the Mark as Solved option, to make this thread as solved.

Thanks and welcome to ExcelForum

[sktneer]

Please find the attached sheet to see the ways around to achieve this.

[popipipo]

Or you can use a pivot table