Org Data - Hierarchy Drill Down???

Are the supervisor's listed in ascending order?

In your example Mike A is the highest level supervisor followed by Mary B followed by Tessa A followed by Rick Y ?

If you chose Tessa A then you'd want to list employees 444, 222, 111 and 123?

There's a workbook with formula- and VBA-based methods to do this at https://app.box.com/s/lw3lfk71kbc121ac768o

The IDs must be unique; names are fluff.

Hmmm...

After reading the post again I don't think I follow the logic of the hierarchy.

I was thinking that all you needed to do was to find the bottom-most instance of the supervisor in column C then return all the employee numbers in column A from that row upwards.

Not sure this is what you had in mind but you can try it...

Data Range

	A	B	C	D	E	F
1	Emp#	Name	Supervisor	-----	Supervisor	Employee #
2	123	Bob Y	Rick Y		Mary B	123
3	111	Jim Z	Rick Y			111
4	222	Tim A	Rick Y			222
5	444	Rick Y	Tessa A			444
6	555	Tessa A	Mary B			555
7	666	Dru F	Mary B			666
8	777	John Q	Mike A

This formula entered in F2:

=IFERROR(INDEX(A$2:INDEX(A$2:A$8,LOOKUP(2,1/(C$2:C$8=E$2),ROW(C$2:C$8)-ROW(C$2)+1)),ROWS(F$2:F2)),"")

Copy down until you get blanks.

In the workbook I linked to, if you run the macro, you can then filter the Report Chain column for Contains 00740 to see Betty Hoshaw and all of her direct and indirect reports.

	G	H	I	J
3	Employee Name	EmpID	Supervisor Name	Reporting Chain by ID
51	******Hoshaw, Betty L	00740	Oskvarek, John J	01240|00511|00767|00740
52	********Moore, Brittany L	00361	Hoshaw, Betty L	01240|00511|00767|00740|00361
53	********Howse, Renee C	00859	Hoshaw, Betty L	01240|00511|00767|00740|00859
54	**********Thomas, Christine B	00100	Howse, Renee C	01240|00511|00767|00740|00859|00100
55	**********Meisenburg, Candace L	00209	Howse, Renee C	01240|00511|00767|00740|00859|00209
56	**********Rehberg, Jennifer M	00221	Howse, Renee C	01240|00511|00767|00740|00859|00221
57	**********Rowan, Andrea L	00456	Howse, Renee C	01240|00511|00767|00740|00859|00456
58	**********Menge, Tracey A	00564	Howse, Renee C	01240|00511|00767|00740|00859|00564
59	**********Morley, Diane M	00568	Howse, Renee C	01240|00511|00767|00740|00859|00568
60	**********Goff, Lisa I	00794	Howse, Renee C	01240|00511|00767|00740|00859|00794
61	**********Loucks, Karl A	00813	Howse, Renee C	01240|00511|00767|00740|00859|00813
62	**********List, James A	00944	Howse, Renee C	01240|00511|00767|00740|00859|00944
63	**********Nguyen, Maily T	00966	Howse, Renee C	01240|00511|00767|00740|00859|00966
64	********Sather, Joan M	00866	Hoshaw, Betty L	01240|00511|00767|00740|00866
65	**********Noonan, Joan L	00103	Sather, Joan M	01240|00511|00767|00740|00866|00103
66	**********Anderson, Lynn M	00269	Sather, Joan M	01240|00511|00767|00740|00866|00269
67	**********Barker, Nina M	00560	Sather, Joan M	01240|00511|00767|00740|00866|00560
68	**********Nash, Kristin M	00744	Sather, Joan M	01240|00511|00767|00740|00866|00744
69	**********Nemmers, Stephanie H	00748	Sather, Joan M	01240|00511|00767|00740|00866|00748
70	**********Neese, Bridget M	00999	Sather, Joan M	01240|00511|00767|00740|00866|00999
71	**********Mendez, Sarah M	01140	Sather, Joan M	01240|00511|00767|00740|00866|01140
72	**********Chester, Tiffany A	01251	Sather, Joan M	01240|00511|00767|00740|00866|01251
73	********Carlson, Lori A	01232	Hoshaw, Betty L	01240|00511|00767|00740|01232

Ah, I see -- it needs a mod to use the employee ID as the key. Dunno why I didn't do that to begin with.

If it otherwise works, you could add the employee's middle initial or full middle name to distinguish.

I didn't think I was on the right track.

So, I guess I don't understand what you're trying to do.

Maybe shg has the solution.

Good luck!