Thread: Staff Hierarchy Problem

I have table of 800 staff; Col A = Name, Col B = Grade, Col C = Supervisor.
I'm trying to create a table which will have these columns plus extra columns to enable me to filter for staff reporting to a particular supervisor.
The supervisor isn't always on the next highest grade; they can sometimes be 3 or 4 grades higher.
The table I have in mind looks something like:

Name Grade Supervisor GradeA GradeB GradeC GradeD GradeE
Smith A Jones Jones Black Green
Jones C Black Black Green
Black D Green Green
Brown B Black Black Green
Green E

I've managed to get most of the way there using a long function incorporating several ifs, indexes and matches, but I can't get it to cope with:
- gaps of more than 2 grades between staff and their immediate supervisor (too many nested ifs)
- some members of staff have a supervisor at the same grade, in which case I want to show the latter entry.

I'm not looking for anyone to solve the problem, just to make some suggestions as to the best way forward.

If its any help, my current fomula to find a supervisor by referring to the member of staff in the previous column (Y)
=IF(Y22="-",IF($T22=Z$1,$B22,"-"),IF(INDEX($B:$T,MATCH(INDEX($B:$E,MATCH(IF(AND(Y22="NONE",X22="NONE"),W22,IF(Y22="NONE",X22,Y22)), $B:$B,0),4),$B:$B,0),19)=Z$1,INDEX($B:$E,MATCH(IF(AND(Y22="NONE",X22="NONE"),W22,IF(Y22="NONE",X22,Y 22)),$B:$B,0),4),"NONE"))

Any suggestions greatly appreciated.
Thank you.

Apologies - I will delete this post.