SOLVED

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

%3CLINGO-SUB%20id%3D%22lingo-sub-2758110%22%20slang%3D%22en-US%22%3EHOW%20TO%3A%20Return%20multiple%20results%20if%20cell%20matches%20multiple%20table%20items%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2758110%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20trying%20to%20match%20a%20long%20string%20to%20multiple%20items%20from%20a%20table%20of%20individual%20items.%20So%20for%20example%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20cell%20with%20a%20value%20something%20like%20%22Group1%3BItem1%3BUser3%3BGroup3%3BItem2%3BGroup5%3BUser1%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20table%20with%20values%3C%2FP%3E%3CP%3EGroup1%3C%2FP%3E%3CP%3EGroup2%3C%2FP%3E%3CP%3EGroup3%3C%2FP%3E%3CP%3EGroup4%3C%2FP%3E%3CP%3EGroup5%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20the%20cell%20above%20I'd%20like%20to%20have%20the%20values%20that%20match%20the%20table%20(Group1%2C%20Group3%20%26amp%3B%20Group5)%20pulled%20out%20into%20a%20single%20cell.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%20data-unlink%3D%22true%22%3EUsing%20the%20function%20from%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fexcel%2Fhow-to-quot-if-cell-contains-specific-text-then-return-specific%2Fm-p%2F69655%22%20target%3D%22_self%22%3Ethis%20post%3C%2FA%3E%20I've%20been%20able%20to%20get%20a%20single%20value%20pulled%20out%20but%20I%20can't%20get%20all%20the%20matching%20values%20into%20a%20single%20cell.%3C%2FP%3E%3CP%20data-unlink%3D%22true%22%3E%26nbsp%3B%3C%2FP%3E%3CP%20data-unlink%3D%22true%22%3EAny%20help%20would%20be%20appreciated.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2758110%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2758143%22%20slang%3D%22en-US%22%3ERe%3A%20HOW%20TO%3A%20Return%20multiple%20results%20if%20cell%20matches%20multiple%20table%20items%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2758143%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F580648%22%20target%3D%22_blank%22%3E%40monrobot%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELet's%20say%20that%20the%20cell%20with%20the%20semicolon-delimited%20words%20is%20A1%20and%20that%20the%20table%20with%20individual%20words%20is%20in%20A4%3AA8.%3C%2FP%3E%0A%3CP%3EEnter%20the%20following%20array%20formula%20in%20another%20cell%2C%20confirmed%20with%20%3CSTRONG%3ECtrl%2BShift%2BEnter%3C%2FSTRONG%3E%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DTEXTJOIN(%22%3B%22%2CTRUE%2CIF(ISNUMBER(SEARCH(A4%3AA8%2CA1))%2CA4%3AA8%2C%22%22))%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2758189%22%20slang%3D%22en-US%22%3ERe%3A%20HOW%20TO%3A%20Return%20multiple%20results%20if%20cell%20matches%20multiple%20table%20items%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2758189%22%20slang%3D%22en-US%22%3EThat%20worked%20perfectly.%20Thanks%20very%20much!%3C%2FLINGO-BODY%3E
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!