VLookup issue when using RANK and a Tie occurs

[ehatz]

I have been working with a score sheet so that it will automatically show the Ranked teams in order. Everything seems to work using the VLookup function except when there is a tie. I have attached a sample of the document with the entered scores and rank on the left and then the sorted display or everything in order on the right. Notice that instead of showing the tie for 5th place it shows the 4th place a second time. Anyone have a formula tweak to solve this?Sample.xlsx

[TMS]

A3:

=RANK(C3,$C$3:$C$10,1)+ROW()/1000000

copied down

H3:

=SMALL($A$3:$A$10,ROW()-2)

copied down


Regards, TMS

[Sam Capricci]

Here is a way to break ties =rank(C2,$c2:$c10,0)+countif($c2:c2,c2)-1 and drag down

[Tony Valko]

Try this...

Data Range

	I	J	K
2	Rank	School	Points
3	1	Stc. East	0
4	2	Geneva	5
5	3	Batavia	9
6	4	Stc. North	13
7	5	Larkin	19
8	5	West Chicago	19
9	7	Streamwood	25
10	8	Elgin	29

Enter this formula in I3:

=SMALL(A$3:A$10,ROWS(I$3:I3))

Enter this array formula** in J3 and copy across to K3:

=INDEX(B:B,SMALL(IF($A$3:$A$10=$I3,ROW(A$3:A$10)),COUNTIF($I$3:$I3,$I3)))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Select I3:K3 and copy down.