Multiple Search Formula in Excel [duplicate]

I am trying to find a formula which will search one cell for specific words.

I would like the outcome of the formula to be words I have selected.

The example of this I have is if one cell shows the list of ingredients, and the other cell shows a list of allergens. I need the formula to search the list of ingredients and choose the allergens.

I know that I could use the find function so that it came back with one result and then concatenate the results but there must be a more simple way!

Thanks

If you have Office 365 Excel which introduced TEXTJOIN, You can use it in an array formula:

=TEXTJOIN(", ",,IF(ISNUMBER(FIND( {"GLUTEN","PEANUTS"},C3)),{"GLUTEN","PEANUTS"},""))

Being an Array formula it needs to be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode.

This will iterate the two names and test the string with Find. If found then Find returnd a number if not an error. We find where it returns a number and then the second array will return the correct value to the TEXT JOIN

But a better practice would be to create a table with the allergens listed:

Then you can refer to that list in the TEXTJOIN formula instead of maintaining it in the formula itself:

=TEXTJOIN(", ",,IF(ISNUMBER(SEARCH( ALLERGEN[ALLERGENS],C3)),ALLERGEN[ALLERGENS],""))

Still an array formula, and I switched to SEARCH to deal with the difference of capitalization.

Now, as new allergens are needed the they can be added to the list without the need to update the formula.

In Cell C6 you can use the below formula,

=MID(C3,FIND("Gluten",C3),LEN("Gluten"))&", "&MID(C3,FIND("Peanuts",C3),LEN("Peanuts"))

you can add more allergens to serch for by adding the below onto the end of the formula:

&", "&MID(C3,FIND("Another Allergen",C3),LEN("Another Allergen"))

And repeat as needed.

You can also error proof this for when you list an allergen which may not be in the ingredients:

=IFERROR(MID(C3,FIND("Gluten",C3),LEN("Gluten")),"")&", "&IFERROR(MID(C3,FIND("Peanuts",C3),LEN("Peanuts")),"")&", "&IFERROR(MID(C3,FIND("Another Allergen",C3),LEN("Another Allergen")),"")

Because this uses FIND the search pattern is case sensitive, you may want to consider swapping there for the SEARCH function which still takes the same 2 arguments in the same order.