Matching multiple criteria of two different data sets
Situation: I have a project intake form asking for what INDUSTRY and TOPIC expertise would be relevant to the project - when completing, you can select multiple relevant industries or topics. Separately, I have a sheet with all the team members and their respective INDUSTRY and TOPIC expertise. When someone completes the intake form, I want one column to populate automatically with all the POSSIBLE team members that have relevant expertise.
The only way I can think of doing it is having the intake form sheet break up each of the individual criteria selected into different rows, then you must have something like a JOIN COLLECT or HAS function to see if the criteria exists for each team member. But I'm a little lost on how to get to in essence a list of all the team members that have any overlapping experience, in one column.
The project intake form:
The list of team members looks like this:
Any clever ideas?
Help Article Resources
Check out the Formula Handbook template!