Help! If contains formula multiple options.

[giuliost]

Hi, I need to do a formula to return different words like this: IF contains the word "Apple" then return "Fruit", or IF contains "plate" return "Restaurants", or IF contains the word "Gym" return the word "Fitness". and so on in the same cell (it is a list so i can drag down the formula). I tried different options but I have always Errors. Is it possible? Anyone that can help? Thanks!

[#2]

@giuliost 

Your question isn't entirely clear to me. Here is the simplest scenario: I created a table with the items and their categories and named it Items. You can then use VLOOKUP:

The formula in F2 is

=VLOOKUP(F2,Items,2,FALSE)

If you have Microsoft 365 or Office 2021 you can also use XLOOKUP:

=XLOOKUP(E2,Items[Item],Items[Category])

[#3]

@giuliost 

For such layout

formula could be

=INDEX(
   Dict[category],
   SUM(  ISNUMBER(SEARCH(Dict[word], [@Phrase]) ) *
         SEQUENCE( ROWS(Dict[word] ) )  ) )

[#4]

Hi, Thanks @HansVogelaar and @SergeiBaklan for the answers and sorry if I wasn't clear: I would like to "create" the category (column B) based on the word in column A (not only the exact match but also if the word that i'm looking for is within a sentence). For example if I have in cell A2 > "Lime mobility 5.1.22 3 $" then I would like the column B to return me "Mobility", or if I have in cell A3 "coffee bar 4.1.22 4$" column B return "Food". I tried: =IF(ISNUMBER(SEARCH("Lime",A)),"Mobility") and it worked but couldn't find a way to add more variables. Thanks 

[#5]

@giuliost 

As previous time I'd suggest to create Dictionary table, not to hardcode texts in formulas.

slightly modified formula in the left table

=LET(
  catN, ISNUMBER(SEARCH(Dict[word], [@Phrase]) ) *
         SEQUENCE( ROWS(Dict[word] ) ),
  catNF, INDEX( FILTER(catN, catN), 1),
   catW, INDEX( Dict[category], catNF),
   IF( catNF, catW, "-") )

[#6]

Thanks @SergeiBaklan ! Great Solution! But when I'm trying to use it in a lighter version of excel the formula doesn't work. Can you please help me with that? Thanks

[#7 · Accepted solution]

@giuliost 

The attached version will work in older versions of Excel too, but it will return only one category.

[#8]

@giuliost 

Strange, your tag is "Excel for web", it shall work on it.

[#9]

It worked the Hans Vogelaar option but with only one category. I'll try again.

[#10]

@giuliost 

For Excel 2019 and later, you can use TEXTJOIN to return multiple categories:

=TEXTJOIN(", ",TRUE,IF(ISNUMBER(SEARCH(Dict[word],[@Phrase])),Dict[category],""))

For Excel 2016 and earlier, you'd probably need a custom VBA function.

[#11]

Simple solution is to use Vlookup instead of Multiple nested if

[#12]

@Donald_Genes_ 

Could you please give more details / sample ?