0

I have data in an excel sheet, which follows the below pattern:

enter image description here

As you can see, each employee ha a manager, who is an employee himself. There are employees reporting to an employee who reports to another (for eg. H reports to D, who reports to B, who in turn reports to E reporting to Z.

What I am looking for is a way I can find all the employees who are "under" a given person. For example, if I supply the formula with E, then it should find me the employees who report to E (A, B and I), those who report to A/B/I (C, D), those who report to C/D (H).

I am not too confident with my excel skills, having used only basic VLOOKUP, but have failed to come up with a way to do this sort of lookups within lookups.

Jay
  • 1,980
  • 1
  • 13
  • 23

3 Answers3

1

With Microsoft365, I used the following:

enter image description here

Formula in D2:

=TRANSPOSE(FILTER(A$2:A$12,IFERROR(MMULT(--(B$2:B$12=D1#),SEQUENCE(COUNTA(D1#),,,0)),B$2:B$12=D1),""))

Dragged down untill you hit empty cells.

JvdV
  • 70,606
  • 8
  • 39
  • 70
  • H reports to D. Why is it under C? – Jay Apr 20 '21 at 06:13
  • It's not perse under `C`. It's just those who report to any of those in the line above them @Jayachandran – JvdV Apr 20 '21 at 06:15
  • Thank You for your response. Upvoting. I will accept if this solution is applicable to Excel 2013. – Jay Apr 20 '21 at 06:19
  • 1
    @Jayachandran, it won't be. It's a Microsoft365 solution. I'd recommend adding your version of Excel to your tags to specify this. – JvdV Apr 20 '21 at 06:22
0

In E2, array formula (Ctrl+Shift+Enter) copied across right to G2 :

=TEXTJOIN(", ",,IF(ISNUMBER(SEARCH($B2:$B12,D2)),$A2:$A12,""))

enter image description here

bosco_yip
  • 3,762
  • 2
  • 5
  • 10
0

For Excel2013 you can use AGGREGATE() function. As per my below screenshot use formulas

D2=IFERROR(INDEX($A$2:$A$12,AGGREGATE(15,6,ROW($1:$12)/($B$2:$B$12=$D$1),ROW(1:1))),"")
E2=IFERROR(INDEX($A$2:$A$12,AGGREGATE(15,6,ROW($1:$12)/($B$2:$B$12=D2),ROW($1:$1))),"")

Copy down and across as needed. If you want to show values in same cell with comma separated then you need to use TEXTJOIN() custom function for Excel2013 as Textjoin is not available on Excel2013. Here is link of TEXTJOIN() from @Scott Craner

TextJoin UDF For Excel 2013

enter image description here

Harun24hr
  • 30,391
  • 4
  • 21
  • 36