Returning multiple results for a cell with multiple values

Eric Luna

I have two sheets: one is a form collection table and the other is a reference table.

One of the fields (Code) entered in the form collection table will determine who is responsible for the request submitted. The Code cell can have multiple values (separated by a comma and space). It is possible to have multiple people responsible for one request if that request has multiple codes.

I want to bring the resulting person(s) responsible for each code to the collection table into one cell (Person Responsible column).

What is the best way to look up the results in the reference table and return all the values into the appropriate cell in form collection table?

Here is the form collection table:

Here is the refence table:

Paul Newcome

Try something along the lines of

=JOIN(COLLECT({Ref Sheet Name Column}, {Ref Sheet Number Column}, HAS(SUBSTITUTE(Code@row, ", ", CHAR(10)), @cell)), ", ")