Pls help me find the right formula for this complicated worksheet.

Hi Everyone

Can anyone pls help me find the right formula for the results I want in the following photo? I'm getting lots of errors, so I'd be appreciated if someone can help me. The results I want are highlighted. The details are as follows.

Untitled.png

I'd like to calculate percentage of the amount based on the rank of the staff that are being reported to. So first of all, we have to check if one staff ("A") in result table are reporting to another staff ("B") in lookup table 1. If yes, then we'll continue to check the rank of "B" in the same table. Next, based on the rank of "B", we'll check % in lookup table 2, and calculate the amount in 1st column (Column H). For example, if the rank of "B" is BB, then 10% of AMT (column G) will be shown in "1ST" column. For the "2nd" Column, we'll have to check if "B" are reporting to another staff. If yes, we'll repeat the same process. If not, then we'll stop calculating.

Kindly see the results of ID A00007.
A00007 is reporting to A00011 whose the rank is BB. For BB rank, the percentage is 10%, so the correct amount is $1,000.00 in 1st column. And we see that A00011 is reporting to A00009 whose the rank is CC. So we have to continue calculating the percentage in 2nd column. CC rank gets 15%, so it's $1,500.00. A00009 also reports to another staff which ID code is A00004 and the rank is DD. So 20% of $10,000.00 will be filled in 3rd column.
I want to stop the process of calculating once a staff doesn't report to another one.

I've tried many formula. But I can't seem to get them right. Pls help me. Thanks.

Pl read yellow banner on top of this page.

Please try at H3

=IFERROR($G3*INDEX($B$9:$B$11,MATCH(VLOOKUP(VLOOKUP($F3,$A$3:$C$6,3,0),$A$3:$C$6,2,0),$A$9:$A$11,)+COLUMNS($H3:H3)-1),0)

H3
=iferror($g3*index($b$9:$b$11,match(vlookup(vlookup($f3,$a$3:$c$6,3,0),$a$3:$c$6,2,0),$a$9:$a$11,)),0)

i3
=iferror($g3*index($b$9:$b$11,match(vlookup(vlookup(vlookup($f3,$a$3:$c$6,3,0),$a$3:$c$6,3,0),$a$3:$c$6,2,0),$a$9:$a$11,)),0)

j3
=iferror($g3*index($b$9:$b$11,match(vlookup(vlookup(vlookup(vlookup($f3,$a$3:$c$6,3,0),$a$3:$c$6,3,0),$a$3:$c$6,3,0),$a$3:$c$6,2,0),$a$9:$a$11,)),0)