Rankings Dynamic Sorting

[PostTIT]

So I've looked literally everywhere - by which I mean on the first page of Google and no further.

I have sheet 1 that does scores and points and more scores for each team (column C5:C54). These scores lead to a total score in column N5:N54. Thus Column O5:O54 shows the ranking by the RANK function (that's the easy bit that I've got sorted).

I then want to have the rankings sheet as a separate sheet to display them nicely formatted to the competitors. So I currently have a table set up on sheeet 2 that uses INDEX and MATCH to find the corresponding rank number from sheet 2 column B2:B51 and display the name beside.

However my issue is that there could easily be duplicates of each rank (sheet 1 displays multiple of the same rank sometimes no bother) and the INDEX and MATCH does not allow for this and so I am getting a return of #N/A in sheet 2 sometimes.

Is this requiring some sort of SORT function to first of all take the rankings as they are with the duplicates that may occur, as opposed to having sheet 1 having a fixed rankings list 1:50? Ideally having duplicates take the form of: 1, 2, 3, 3, 5, 5, 7, etc...

Other sources seem to not be doing exactly as I'm wanting.

Any help would be great, thanks.​

 

[StephenCrump]

So I've looked literally everywhere - by which I mean on the first page of Google and no further.

See if you can adapt this:

A2: =SMALL(D$2:D$9,ROWS(2:$2))
B2: {=INDEX(E$2:E$9,SMALL(IF(D$2:D$9=A2,ROW(D$2:D$9)-ROW(D$1)),COUNTIF($A$2:A2,A2)))} (Array-entered)

Excel 2010
	A	B	C	D	E
1	Output			Table
2	1	Mary		1	Mary
3	2	Joe		3	Jane
4	3	Jane		2	Joe
5	3	Jill		5	Bob
6	5	Bob		3	Jill
7	5	Sam		5	Sam
8	5	Nick		8	Mike
9	8	Mike		5	Nick
Sheet1

 

[PostTIT]

Worked perfectly, thank you very much. Maybe next time I'll search google page 2 as well.

I had to extend the formula to say:
B2: {=INDEX(E$2:E$9,SMALL(IF(D$2:D$9=A2,ROW(D$2:D$9)-ROW(D$1)+1),COUNTIF($A$2:A2,A2)))} (Array-entered)

Then it worked. Some Excel magic wasn't matching up correctly before but now it is - so Excel-Gandalf is now happy.

Thanks.

 

[StephenCrump]

Worked perfectly, thank you very much.

You're welcome.

Maybe next time I'll search google page 2 as well.

... or just come straight to this Forum.