Search for assigned manager, then get list of direct reports

Mary A

Hi - I am trying to create a formula in a Contact List column that returns all the people reporting to a manager.

I do not know the best way to build this formula. Would someone kindly help me with the best way to do this, and possibly get me started with a formula?

In the images below is my prototype. I have 5 employees total, and 2 are managers (Amy & Emmit).

In the "Lab Members" column, I would like to have a formula search the "Manager (PI)" column for the name of the Manager (Amy & Emmit in this example), and then return all of the employees the from the Contact List in that cell.

I would like to go from this -

To this - where if I look at Amy or Emmit's "Lab Members" column, there is a list of all their employees from the Contact List.

Jenna Bailey

I would create a separate sheet where all of this is listed where the "Manager" is in column 1 and the "Lab Member" is in column 2 then in your main source sheet do a vlookup that returns the value of column 2

Mary A

Thanks. That is unfortunately not a viable solution because that means I would have to keep 2 sheets with "Manager" and "Lab Member" names updated. The reason this comes up in the first place is that managers and members change often, and when new "lab members" join, it is all entered on one sheet.