Search Multiple Word String and Return Categories

[aclspeers]

Hello,

Completely lost on this one. I'm trying to parse through a string with multiple items, compare each against a list of categories, and return which categories are included in the string. I've included an example:

Categories:

Berries	Raspberries, Blueberries, Blackberries
Tropical Fruit	Star Fruit, Bananas
Everyday Fruit	Apples, Oranges

List:	Return:
Apples, Bananas, Raspberries	Everyday Fruit, Tropical Fruit, Berries
Star Fruit	Tropical Fruit
Blueberries	Berries
Oranges, Blackberries	Everyday Fruit, Berries

[Aardigspook]

Welcome to the forum.

Assuming your table represents columns A and B, put this in C2 and copy down:

=TRIM(IF(COUNT(SEARCH({"Raspberries","Blueberries","Blackberries"},A4)),"Berries","") &" "& IF(COUNT(SEARCH({"Star Fruit","Banana"},A4)),"Tropical Fruit","") &" "& IF(COUNT(SEARCH({"Apple","Orange"},A4)),"Everyday Fruit",""))

The only problem is that this doesn't put a comma between your categories. If you want the comma, you can replace the &" "& with &", "& but then you'll get results such as ', Tropical Fruit,' or 'Berries, ,' or 'Berries, , Everyday Fruit' - it's up to you which you prefer.

Hope that helps.