Employee Hierarch query

[mart_mrexcel]

I have a database hierachy where an employee is reporting to a Manager, i wanted get the value (Emp ID) of the employee's LAST Manager in the hierarchy.

In the example, Employee 1022 is reporting to Emp 4901 with "Manager" or "Mgr" in the position, however, the 2nd line manager of emp 1022 (i.e. the manager of emp 4901) is reporting to emp 4693 where it has "Manager" or "Mgr" in the position title also. Given this, the expected value of employee's LAST manager is Emp 4693. Basically, i want to get the emp id of the employee's Last manager in the hierarchy with "Manager" or "Mgr" position in the title.

Book1
	A	B	C	D	E	F	G	H
1
2	Database				Hierarchy
3	Emp ID	CURRENT Position Title	Mgr's ID		Emp ID	Mgr's ID	Mgr's Position	With "Manager" or "Mgr" in position?
4	1022	Specialist III, Process Training	4901		1022	4901	Manager, PP	1
5	4693	Senior Manager, Polymer Operations	5236		4901	4693	Senior Manager, Polymer Operations	8
6	4901	Manager, PP	4693		4693	5236	GM, Manufacturing
7	7819	VP - Manufacturing	NO_MANAGER		5236	7819	VP - Manufacturing
8	10737	VP, Downstream	7819
9	13655	Managing Director, Cristal Int. Hold. BV	204751
10	16573	Manager, Debt Management	208855		Expected Output	4693
11	19491	Sr Specialist I, EBS Function Bus Solution	204158
12	5236	GM, Manufacturing	7819
Sheet1

Cell Formulas
Range	Formula
F4:F7	F4	=XLOOKUP(E4,$A:$A,$C:$C)
G4:G7	G4	=XLOOKUP(F4,$A:$A,$B:$B)
H4:H7	H4	=IFERROR(SEARCH(@{"Manager","Mgr"},TRIM(G4),1),"")

Named Ranges
Name	Refers To	Cells
_FilterDatabase	=Sheet1!$A$3:$G$3302	F4:G7