SOLVED

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

Occasional Contributor

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 Replies
best response confirmed by monrobot (Occasional Contributor)
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,""))

That worked perfectly. Thanks very much!