Forum Discussion
HOW TO: Return multiple results if cell matches multiple table items
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.
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,""))
2 Replies
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!
