Forum Discussion
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!
The attached version will work in older versions of Excel too, but it will return only one category.
11 Replies
- Simple solution is to use Vlookup instead of Multiple nested if
Could you please give more details / sample ?
For such layout
formula could be
=INDEX( Dict[category], SUM( ISNUMBER(SEARCH(Dict[word], [@Phrase]) ) * SEQUENCE( ROWS(Dict[word] ) ) ) )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])
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
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, "-") )
