formula to lookup rank (with ties)

[Phily915]

I need a formula that will lookup a rank. The caveat is that these ranks include ties and are denoted with "T -". I have attached a worksheet that shows my dilemma.

Column A: Team Ranks
Column B: Rank Tie-Breaker
Column C: Teams
Column D: Places the Teams in order based on Rank/Tie-Breaker Value

I'd like this formula to Rank 1-9 and/or T-1 - whatever. Example: If Team A is T-1 and Team D is also T-1, then the tie-breaker will come into effect. In this case, Team A's tie-breaker value is 1 and Team D's tie-breaker value is 5. Therefore, Team A would be ranked higher than Team D. These results will be placed in column D.

[Sam Capricci]

This is a formula that will generally rank a set of data and break ties. How to apply it for your issue is up to you. =RANK(B3,$B$3:$B$11,0)+COUNTIF($B$3:B3,B3)-1
Hope that helps.

[Tony Valko]

Try it like this...

Data Range

	A	B	C	D	E
2	Rank	Tie Breaker	Final Rank	Team	VLOOKUP/RANK ORDER
3	1	1	1	A	A
4	6	3	6	B	D
5	4	2	4	C	F
6	1	5	2	D	C
7	4	4	5	E	E
8	1	9	3	F	B
9	6	6	7	G	G
10	6	7	8	H	H
11	6	8	9	I	I

In column A get rid of the "T - " and simply enter the numeric rank.

Insert a new column C and calculate the Final Rank that includes a 1st tie breaker. Enter this formula in C3 and copy down as needed:

=RANK(A3,A$3:A$11,1)+SUMPRODUCT(--(A3=A$3:A$11),--(B3>B$3:B$11))

Then, enter this formula in E3 and copy down as needed:

=INDEX(D$3:D$11,MATCH(ROWS(F$3:F3),C$3:C$11,0))

[Phily915]

thanks tony,
ill give it a try i a little while!