how to make IF statement based on one word within a phrase to return another cell's value-CodePudding

I have this formula:

=IF(OR(L7="FOOD",L7="GROCERIES"),K7,0)

It only works if for example "FOOD" is the only content. Tonight's Food returns false. I need it to return true regardless of other words.

CodePudding user response：

What about this:

=IF(OR(NOT(ISERROR(FIND("FOOD",L7))),NOT(ISERROR(FIND("GROCERIES",L7)))),K7,0)

Some explanation:

FIND("FOOD",L7) either finds the word "FOOD" (in capitals) and gives a number, or it yields an error.
ISERROR() checks for an error or not.

CodePudding user response：

I would always put the criteria in an extra list - in the screenshot below I named that list 'keywords'

Then you can use this formula - if you have Excel 365 (it spills down) =BYROW(A2:A4,LAMBDA(d,SUM(--ISNUMBER(FIND(keywords,d)))))

Or if you don't have Excel 365 - then you have to drag down =SUM(--ISNUMBER(FIND(keywords,A2)))

You will then use this base formula in your IF-statement like this:

=IF(SUM(--ISNUMBER(SEARCH(keywords,L7))),K7,0)

FIND is case-sensitive, SEARCH would find "Food" as well.