Return table column value for all rows with one lookup value

[megera716]

Table2 contains all our employees, and various data on each employee, including their manager's name. I want to look up the manager's name and return all the employees who report to them. Reading here about the FILTER and TOROW functions, it seems like this should work but it's not. A Pivot Table doesn't work for my end goal here, although I will use a Pivot grouped by manager to manually copy and paste if I have no other choice (but I have over 700 employees to do this with so I don't want to go that route if I don't have to).

In cell B7, I have formula {=FILTER(Table2[Employee],Table2[Manager]=A7)} but when I drag the formula down to row 8, 9, 10, 11.... it just returns the same value that was on Row 7.

It comes out looking like Rows 7:11, but I want it to look like Rows 14:18.

 

[Fluff]

Do not use Ctrl Shift Enter to confirm the formula, just use Enter & do not copy it down.

 

[megera716]

OMG, I guess sometimes the right solution is the simplest one! Thank you, that worked perfectly

 

[Fluff]

You're welcome & thanks for the feedback.