Forum Discussion
Help! If contains formula multiple options.
The attached version will work in older versions of Excel too, but it will return only one category.
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, "-") )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
The attached version will work in older versions of Excel too, but it will return only one category.
