I've found a couple of formulas to produce a staff hierarchy tool. Basically the idea is that you enter an employee number and anyone reporting into that person would come back in the results - as would anyone who reports into them, who reports into them, etc

So if I put the chief exec's number in then everyone would come back in the results. Similarly, if I put in a lower level employee number then only their results would come back because nobody reported into them. The problem that I've got is the way the formula works appears to be very much dependant on the order of the data.

If the manager is lower down on the list than the person reporting into them then that record is ignored. Again using my example of the chief exec, if one of their direct reports appeared higher than them on the list then I could miss chunks of information because the person down from the chief exec has a dozen people into them, who have people reporting into them, etc etc.

I can't sort the data by employee number because even though you've been in the job for years, there may be some young whipper-snappers who have come in above you. With the formula that I'm using if your manager has a employee number that comes after yours, then you don't appear on the report.

Is there any way to alter the formulas so that it picks up those that it missed the first time around?

hierarchy_extractor.xlsx