Help! If contains formula multiple options.

New Contributor

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!

11 Replies


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


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




For such layout


formula could be

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

Hi, Thanks @Hans Vogelaar and @Sergei Baklan 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 


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


slightly modified formula in the left table

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

Thanks @Sergei Baklan ! 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? ThanksScreenshot 2022-01-05 at 22.38.47.png


best response confirmed by giuliost (New Contributor)


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


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

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


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.

Simple solution is to use Vlookup instead of Multiple nested if


Could you please give more details / sample ?