Assistance with complex VLOOKUP formula

Hi everyone,
I need some help with a formula I'm using to fill in the Manager’s email.

Here is the formula:
=IF(F2="", "", VLOOKUP(IF(VLOOKUP(F2, $A$2:$I$1400, 4, FALSE) = 0, VLOOKUP(F2, $A$2:$I$1400, 6, FALSE), F2), $A$2:$I$1400, 9, FALSE))
Context:
Rank 0: Roles below the Department Manager level are ranked as 0 in the 4th column (‘Rank’).
Current Process: If an executive reports to a Senior Executive (Rank 0), the formula checks the 4th column. If the Senior Executive has a Rank 0, it looks at the 6th column instead (to get the employee number the Senior Executive reports to).
Example: For employee ‘7586’ with email ‘p22#gmail.com’, the formula currently fills ‘p23#gmail.com’. It first looks at the 4th column of 1577, finds 0, then takes the value in the 6th column (0209) and uses that for the final VLOOKUP.
Needed Change: I need the formula to perform an additional VLOOKUP. It should check the 4th column for employee 0209. If it’s still 0, it should look at the 6th column instead.
Goal: The new formula should fill ‘p26#gmail.com’ as the manager’s email for employee 7586, not ‘p23#gmail.com’.
Any suggestions on how to adjust the formula to achieve this?

Thanks in advance!