Finding all instances associated with a multi item data cell

KevinMio

Hi,

I cannot seem to find an approach to search another sheet for an item in a column that has cells having multiple entries and then return the associated entry from another column. I tried combinations of (Join / distinct / collect) as well as (find / has) but no luck. In the image of the sheet I would like to enter the affected item and receive back all instances of the issue, any suggestions? (perhaps a search for CTNB01117 would be a good example). Thanks.

Paul Newcome

Try this:

=JOIN(COLLECT({Issue}, {Affected Item}, CONTAINS("CTNB01117", @cell)), ", ")

Paul Newcome

Try this:

=JOIN(COLLECT({Issue}, {Affected Item}, CONTAINS("CTNB01117", @cell)), ", ")

KevinMio

That was it - I was close but mixed up some commas and parenthesis. Thank you.

Paul Newcome

Happy to help. 👍️