SOLVED

# Help! If contains formula multiple options.

Copper Contributor

# 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 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

# Re: Help! If contains formula multiple options.

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])

# Re: Help! If contains formula multiple options.

For such layout formula could be

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

# Re: Help! If contains formula multiple options.

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

# Re: Help! If contains formula multiple options.

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, "-") )``````

# Re: Help! If contains formula multiple options.

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? Thanks best response confirmed by giuliost (Copper Contributor)
Solution

# Re: Help! If contains formula multiple options.

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

# Re: Help! If contains formula multiple options.

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

# Re: Help! If contains formula multiple options.

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

# Re: Help! If contains formula multiple options.

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.

# Re: Help! If contains formula multiple options.

Simple solution is to use Vlookup instead of Multiple nested if

# Re: Help! If contains formula multiple options.

Could you please give more details / sample ?