Joining two data sets using a multiselect column

JLC

Hoping for either formula help or suggestions for how to restructure the data so that I can make this happen! Please let me know if I can clarify anything and thanks in advance to anyone willing to take a stab at this.

Main sheet: list of events; one row per event, with the following columns:

1. Event name (text)
2. Attendee names (multi-select dropdown)
3. Departments represented at event (this is what I need help formulating)

Data sheet: list of all attendees' respective departments; one row per attendee, with the following columns:

1. Attendee name (text)
2. Attendee department (text)

Example of desired output:

1. Event A
2. Attendee 1, Attendee 2, Attendee 3
3. Attendee 1's department, Attendee 2's department, Attendee 3's department

How can I connect these two sheets so that I can get either lists or counts of the departments present at each event? Alternatively, I am also okay with having column #3 be something like "Department A present?", column #4 be "Department B present?", column #5 be "Department C present?", etc.

Some thoughts I had, whether they'd help or not, include: having one data sheet of attendees per department, renaming all attendees to include their department in their name

Deric

This is what i came up with: =JOIN(DISTINCT(COLLECT({Data_AttendeeDepartment}, {Data_AttendeeName}, HAS([Attendee names]@row, @cell))), CHAR(10))