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
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.
      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Jan 05, 2022

        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.

Resources