Rank with Multiple Criteria

[parag385]

Hello,

I have data set where I want to rank individuals of each department separately based on their designation and then based on their years of experience. Please refer to below excel where I want the output in column E, F & G. Also, if 2 employees of same department have same designation & same years of experience then they should get different rank (like for Lyra & Peter of human resource department, highlighted in green).

I tried different excel functions but was unable to get the desired result.

Thanking you in advance.

Data - Rank.xlsx
	A	B	C	D	E	F	G
1	Name	Department	Designation	Number of years with company	Rank - Finance	Rank - HR	Rank - Marketing
2	Ben	Finance	Director	5	1
3	Danny	Finance	Analyst	3	4
4	David	Marketing	Vice President	7			2
5	Jacob	Human Resource	Director	4		2
6	Jenny	Human Resource	Managing Director	2		1
7	Leo	Human Resource	Vice President	6		3
8	Lyra	Human Resource	Associate	1		4
9	Meena	Marketing	Director	5			1
10	Patrick	Finance	Associate	1	2
11	Peter	Human Resource	Associate	1		5
12	Sam	Marketing	Associate	6			3
13	Sammy	Finance	Analyst	4	3
14
15
16		Order of Designation
17		Managing Director
18		Director
19		Vice President
20		Associate
21		Analyst
Sheet1

 

[Fluff]

Hi & welcome to MrExcel.
How about

Fluff.xlsm
	A	B	C	D	E	F	G
1	Name	Department	Designation	Number of years with company	Finance	Human Resource	Marketing
2	Ben	Finance	Director	5	1
3	Danny	Finance	Analyst	3	4
4	David	Marketing	Vice President	7			2
5	Jacob	Human Resource	Director	4		2
6	Jenny	Human Resource	Managing Director	2		1
7	Leo	Human Resource	Vice President	6		3
8	Lyra	Human Resource	Associate	1		4
9	Meena	Marketing	Director	5			1
10	Patrick	Finance	Associate	1	2
11	Peter	Human Resource	Associate	1		5
12	Sam	Marketing	Associate	6			3
13	Sammy	Finance	Analyst	4	3
14
15
16		Order of Designation
17		Managing Director
18		Director
19		Vice President
20		Associate
21		Analyst
Sheet6

Cell Formulas
Range	Formula
E2:G13	E2	=LET(f,FILTER($A$2:$D$13,$B$2:$B$13=E$1),IF($B2=E$1,XMATCH($A2,INDEX(SORTBY(f,XMATCH(INDEX(f,,3),$B$17:$B$21),1,INDEX(f,,4),-1),,1)),""))

 

[Cubist]

Here's an array option.

Book1
	A	B	C	D	E	F	G
1	Name	Department	Designation	Number of years with company	Finance	Human Resource	Marketing
2	Ben	Finance	Director	5	1
3	Danny	Finance	Analyst	3	4
4	David	Marketing	Vice President	7			2
5	Jacob	Human Resource	Director	4		2
6	Jenny	Human Resource	Managing Director	2		1
7	Leo	Human Resource	Vice President	6		3
8	Lyra	Human Resource	Associate	1		4
9	Meena	Marketing	Director	5			1
10	Patrick	Finance	Associate	1	2
11	Peter	Human Resource	Associate	1		5
12	Sam	Marketing	Associate	6			3
13	Sammy	Finance	Analyst	4	3
14
15
16		Order of Designation
17		Managing Director
18		Director
19		Vice President
20		Associate
21		Analyst
Sheet3

Cell Formulas
Range	Formula
E2:G13	E2	=LET(a,$A$2:$A$13,b,$B$2:$B$13=E1,c,$C$2:$C$13,d,$D$2:$D$13,e,$B$17:$B$21, XLOOKUP(IF(b,a),SORTBY(a,XLOOKUP(IF(b,c),e,SEQUENCE(5,,5,-1),0),-1,d,-1),SEQUENCE(ROWS(a)),""))
Dynamic array formulas.