Google Sheets - IF cell contains certain text (or texts) then return values-CodePudding

I've been searching all over and can't get this right. I am trying to create an alert that returns a word or words when certain words are found in a cell. I want the formula to search for specific words in the same cell and when it gets a match it will return a certain value/values.

For example, the formula will look for any of the next words in the cell F10 and then return the correspondent text if found. In case there are two or more matches it will return several answers.

Found "Pizza" or "Hotdog" in F10? - Return "Food"
Found "Apple" or "Banana" in F10? - Return "Fruit"
Found "Coffee" or "Coke" in F10? - Return "Drink"
Found "Pizza" AND "Apple" - Return "Food" AND "Fruit"
Found "Pizza" AND "Apple" AND "Coke" - Return "Food" AND "Fruit" AND "Drink".

Appreciate all help!

CodePudding user response：

Concatenated values

=join(", ",query({if(or(REGEXMATCH( upper($F$10),"PIZZA"), REGEXMATCH( upper($F$10),"HOTDOG")),"FOOD",);if(or(REGEXMATCH(upper($F$10),"APPLE"),REGEXMATCH(upper($F$10),"BANANA")),"FRUIT",);if(or(REGEXMATCH(upper($F$10),"COFFEE"),REGEXMATCH(upper($F$10),"COKE")),"DRINK")}, "select * where Col1 is not null"))

List Values

=query({if(or(REGEXMATCH( upper($F$10),"PIZZA"), REGEXMATCH( upper($F$10),"HOTDOG")),"FOOD",);if(or(REGEXMATCH(upper($F$10),"APPLE"),REGEXMATCH(upper($F$10),"BANANA")),"FRUIT",);if(or(REGEXMATCH(upper($F$10),"COFFEE"),REGEXMATCH(upper($F$10),"COKE")),"DRINK")}, "select * where Col1 is not null")

CodePudding user response：

try:

=TEXTJOIN(", ", 1, INDEX(IFNA(VLOOKUP(SPLIT(B10, ", "), SPLIT({
 {"Pizza"; "Hotdog"}&"×Food"; 
 {"Apple"; "Banana"}&"×Fruit"; 
 {"Coffee"; "Coke"}&"×Drink"}, "×"), 2, 0))))