Forum Discussion

giuliost's avatar
giuliost
Copper Contributor
Jan 05, 2022
Solved

Help! If contains formula multiple options.

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...
excel
Excel for web
Formulas and Functions
giuliost's avatar
giuliost
Copper Contributor
Jan 05, 2022

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 

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jan 05, 2022

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, "-") )
  • giuliost's avatar
    giuliost
    Copper Contributor
    Jan 05, 2022

    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

     

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Jan 05, 2022

      giuliost 

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

      • giuliost's avatar
        giuliost
        Copper Contributor
        Jan 05, 2022
        It worked the Hans Vogelaar option but with only one category. I'll try again.

Resources