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







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)


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:



That worked perfectly. Thanks very much!