Index Match to List All Types of Program Managers Per Team

Question

I have a list of employee data and I want to extract anyone who is any type of "Program Manager". For example, there's "Senior Program Manager", "Technical Program Manager", "UX Program Manager", and I want to get their username.

In this Employee Data table, I am trying to figure how to extract all types of Program Manager per team so my dynamic spreadsheet would look like this:

Team	Program Manager
A-team	kevinchen (Sr. Technical Program Manager)
B-team	lisalu (Program Manager), jengomez (Technical Program Manager)
C-team	joannwynn (Senior Program Manager), jackiechan (UX Program Manager), kenle (Senior Program Manager)

Google Sheet Example

Thanks in advance. 🙏

I have tried using index match and regular expression but I'm getting "#Error!" message. =IF(ISBLANK(C2),"",index(employee_data!B:B,match(1,(employee_data!A:A="A-team")*('list of ppl'!J:J="*Product Manager"),0)))"))))"

Answer 1

You may try:

=let(Σ,filter({A:A,B:B&" ("&C:C&")"},regexmatch(C:C,"Program Manager")),
     byrow(unique(index(Σ,,1)),lambda(Λ,{Λ,join(", ",filter(index(Σ,,2),index(Σ,,1)=Λ))})))