ranking with tie-breaker criteria based on larger or smaller value

[valmir]

Hello everyone
I am using this formula:
=RANK.EQ(J1;$J$1:$J$16)+COUNTIF($J$1:J1;J1)-1
to rank a classification table however, in case of a tie, I want the row with the larger value in column I to rank higher. In this particular example, row 4 should be ranked above row 3 because row 4 has a higher value in column I than row 3. Same problem between rows 11 and 12 and from rows 13 to 16. I have added the current status and the desired outcome. Also, I would like to how it is in case I have to rank with the second criteria based on smaller value in column I). Thanks

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	1	PET	3	3	0	0	11	2	+9	9		1	PET	3	3	0	0	11	2	+9	9
2	2	PRO	3	2	1	0	3	1	+2	7		2	PRO	3	2	1	0	3	1	+2	7
3	3	MAQ	3	2	0	1	4	3	+1	6		3	PRI	2	2	0	0	3	0	+3	6
4	4	PRI	2	2	0	0	3	0	+3	6		4	MAQ	3	2	0	1	4	3	+1	6
5	5	CAA	3	1	2	0	6	2	+4	5		5	CAA	3	1	2	0	6	2	+4	5
6	6	FER	3	1	2	0	4	3	+1	5		6	FER	3	1	2	0	4	3	+1	5
7	7	INT	3	1	1	1	3	3	0	4		7	INT	3	1	1	1	3	3	0	4
8	8	SAG	3	1	1	1	2	2	0	4		8	SAG	3	1	1	1	2	2	0	4
9	9	DES	3	0	3	0	4	4	0	3		9	DES	3	0	3	0	4	4	0	3
10	10	ACA	3	0	2	1	1	2	-1	2		10	ACA	3	0	2	1	1	2	-1	2
11	11	CCU	3	0	2	1	3	5	-2	2		11	SRC	3	0	2	1	1	2	-1	2
12	12	SRC	3	0	2	1	1	2	-1	2		12	CCU	3	0	2	1	3	5	-2	2
13	13	BAI	3	0	1	2	1	9	-8	1		13	WIL	2	0	1	1	1	2	-1	1
14	14	LIB	3	0	1	2	1	5	-4	1		14	SCC	3	0	1	2	0	3	-3	1
15	15	SCC	3	0	1	2	0	3	-3	1		15	LIB	3	0	1	2	1	5	-4	1
16	16	WIL	2	0	1	1	1	2	-1	1		16	BAI	3	0	1	2	1	9	-8	1
Sheet1

 

[AlphaFrog]

=RANK.EQ(J1;$J$1:$J$16)+COUNTIFS($J$1:J1;J1; $I$1:I1;"<"&I1)

 

[valmir]

=RANK.EQ(J1;$J$1:$J$16)+COUNTIFS($J$1:J1;J1; $I$1:I1;"<"&I1)

After applying your formula this is what I am getting. Not only it has no effect but it is also giving me some blanks. Am I doing something wrong?

Book1
	A	B	C	D	E	F	G	H	I
1	PET	3	3	0	0	11	2	+9	9
2	PRO	3	2	1	0	3	1	+2	7
3	MAQ	3	2	0	1	4	3	+1	6
4	PRI	2	2	0	0	3	0	+3	6
5	CAA	3	1	2	0	6	2	+4	5
6
7	INT	3	1	1	1	3	3	0	4
8
9	DES	3	0	3	0	4	4	0	3
10	ACA	3	0	2	1	1	2	-1	2
11	SRC	3	0	2	1	1	2	-1	2
12
13	BAI	3	0	1	2	1	9	-8	1
14	LIB	3	0	1	2	1	5	-4	1
15	SCC	3	0	1	2	0	3	-3	1
16	WIL	2	0	1	1	1	2	-1	1
Sheet1

 

[Phuoc]

Maybe like this:

=RANK.EQ(J1,$J$1:$J$16)+COUNTIFS($J$1:$J$16,J1, $I$1:$I$16,">"&I1)

 

[valmir]

Maybe like this:

=RANK.EQ(J1,$J$1:$J$16)+COUNTIFS($J$1:$J$16,J1, $I$1:$I$16,">"&I1)

With this one this is the result. Still not good

Book1
	A	B	C	D	E	F	G	H	I
1
2	PET	3	3	0	0	11	2	+9	9
3
4	PRI	2	2	0	0	3	0	+3	6
5	MAQ	3	2	0	1	4	3	+1	6
6
7	CAA	3	1	2	0	6	2	+4	5
8
9	DES	3	0	3	0	4	4	0	3
10	ACA	3	0	2	1	1	2	-1	2
11
12
13	BAI	3	0	1	2	1	9	-8	1
14	WIL	2	0	1	1	1	2	-1	1
15
16
Sheet1

 

[Phuoc]

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	1	PET	3	3	0	0	11	2	9	9	1
2	2	PRO	3	2	1	0	3	1	2	7	2
3	3	MAQ	3	2	0	1	4	3	1	6	4
4	4	PRI	2	2	0	0	3	0	3	6	3
5	5	CAA	3	1	2	0	6	2	4	5	5
6	6	FER	3	1	2	0	4	3	1	5	6
7	7	INT	3	1	1	1	3	3	0	4	7
8	8	SAG	3	1	1	1	2	2	0	4	7
9	9	DES	3	0	3	0	4	4	0	3	9
10	10	ACA	3	0	2	1	1	2	-1	2	10
11	11	CCU	3	0	2	1	3	5	-2	2	12
12	12	SRC	3	0	2	1	1	2	-1	2	10
13	13	BAI	3	0	1	2	1	9	-8	1	16
14	14	LIB	3	0	1	2	1	5	-4	1	15
15	15	SCC	3	0	1	2	0	3	-3	1	14
16	16	WIL	2	0	1	1	1	2	-1	1	13
Sheet1

Cell Formulas
Range	Formula
K1:K16	K1	=RANK.EQ(J1,$J$1:$J$16)+COUNTIFS($J$1:$J$16,J1, $I$1:$I$16,">"&I1)

 

[valmir]

=RANK.EQ(J1,$J$1:$J$16)+COUNTIFS($J$1:$J$16,J1, $I$1:$I$16,">"&I1)

Can you please tell me which index match are you using to get that? Probably has to do with the one I am using?
=IFERROR(INDEX($B$1:$J$16;MATCH(A1;$K$1:$K$16;0);1);"") to =IFERROR(INDEX($B$1:$J$16;MATCH(A1;$K$1:$K$16;0);9);"")

 

[valmir]

Can you please tell me which index match are you using to get that? Probably has to do with the one I am using?
=IFERROR(INDEX($B$1:$J$16;MATCH(A1;$K$1:$K$16;0);1);"") to =IFERROR(INDEX($B$1:$J$16;MATCH(A1;$K$1:$K$16;0);9);"")

 

[valmir]

Thanks for the help even though I am stuck with blank rows because the index match formula I am using only shows one value whenever there are two exact maches but I know that it is not your fault. Your formula works perfectly. Thanks again!