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?


  • Leibel S
    Leibel S ✭✭✭✭✭✭

    @Dominique Rose Winther

    You will probably need to create a sheet (or you have one already) with a list of your topics and industries. In that sheet you will create a column that JOINS/COLLECTS all the team members associated with that specific topic/industry.

    Then in your project intake you would be doing a join and collect on the above sheet, pulling the team members that are associated with each of the topics and/or industries of the project.

    Let me know if you need help actually building out the formulas...

  • NikkiOno
    NikkiOno ✭✭✭

    @Leibel Shuchat It seems like I need help with something similar. below is the info:

    I have a completed alterations sheet that has both original alterations with customer name and redo's with customer name that have both been completed. I need to create a sheet or report that matches the customer name with the original alterations to the redo.

    Completed Alterations Sheet (includes original which is Sale Status "PAID" and redo which is Sale Status "REDO".

    The goal is to be able to compare the redo's for an item against the original.

    Let me know if this doesn't make sense.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!