HOW TO: Return multiple results if cell matches multiple table items

[monrobot]

I'm trying to match a long string to multiple items from a table of individual items. So for example

I have a cell with a value something like "Group1;Item1;User3;Group3;Item2;Group5;User1"

I have a table with values

Group1

Group2

Group3

Group4

Group5

For the cell above I'd like to have the values that match the table (Group1, Group3 & Group5) pulled out into a single cell.

Using the function from this post I've been able to get a single value pulled out but I can't get all the matching values into a single cell.

Any help would be appreciated.

[#2 · Accepted solution]

@monrobot 

Let's say that the cell with the semicolon-delimited words is A1 and that the table with individual words is in A4:A8.

Enter the following array formula in another cell, confirmed with Ctrl+Shift+Enter:

=TEXTJOIN(";",TRUE,IF(ISNUMBER(SEARCH(A4:A8,A1)),A4:A8,""))

[#3]

That worked perfectly. Thanks very much!