Optimizing Excel Formula for Dynamic VLookup

Hi, I have this 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))

This formula is meant to fill in the Manager’s email.

Rank 0: Every role below the Department Manager level is ranked as 0 in the 4th column (‘Rank’).

Currently 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 ‘email address removed for privacy reasons’, the formula currently fills ‘email address removed for privacy reasons’. 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 ‘email address removed for privacy reasons’ as the manager’s email for employee 7586, not ‘email address removed for privacy reasons’.