Get excel to list teams automatically (without having to press sort button)

[frnkmurphy]

Hi guys, have searched for this, but am really struggling!

I have a list of teams and a list of points. I need to copy this list further down the page, but have excel automatically list the teams in order of their points. (Without pressing the auto sort!!)

I have managed to rank the number of points, and have used vlookup to get the name of the team next to the value in points. (By getting it to look up the cell with the highest value, and then second highest, using =max() and =large(x:x,2)) It looks like this:

16 Team 1
12 Team 2
12 #N/A
11 Team 4
9 Team 5
9 #N/A

The problem as you can see is that when there are more than one team on the same points, it can only find the first one.

Can anyone help me? I have made a list next to the teams, putting them in alphabetical order, and thought that I might be able to rank first against points and then against the name, but not sure how I would get along with that, as the vlookup is looking for the first, second, third etc value of points. I can email someone the spreadsheet if that helps!!

Hope you can make sense of that!!! Thankyou very much for any insight.

 

[jbeaucaire]

You need a ranking system that takes duplicates into account, like C3 does in this chart. Then regular INDEX/MATCH formulas can re-sort your list automatically:

Excel Workbook
	A	B	C	D	E	F	G
1	16	Team1	3	Raw	Ranked	Team13	23
2	12	Team2	7			Team19	22
3	12	Team3	8			Team1	16
4	11	Team4	10			Team18	16
5	9	Team5	13			Team11	14
6	9	Team6	14			Team12	14
7	11	Team7	11			Team2	12
8	9	Team8	15			Team3	12
9	3	Team9	20			Team16	12
10	4	Team10	19			Team4	11
11	14	Team11	5			Team7	11
12	14	Team12	6			Team17	11
13	23	Team13	1			Team5	9
14	5	Team14	18			Team6	9
15	6	Team15	17			Team8	9
16	12	Team16	9			Team20	7
17	11	Team17	12			Team15	6
18	16	Team18	4			Team14	5
19	22	Team19	2			Team10	4
20	7	Team20	16			Team9	3
Sheet3

 

[frnkmurphy]

Thankyou very much, i've been messing around with that for weeks!!