Forum Discussion
Solved
Need an Excel Formula giving True or False based on the last word in the cell
Checking online I've found these two formulas:
=IF(COUNT(SEARCH({"Avenue","Road"},D2)),"TRUE","FALSE") - This returns TRUE or FALSE if either word is anywhere in the cell.
=RIGHT(D2,LEN(D2)-FIND...
I tried another way. The following formula do not need CSE too.
=IF($D2="","",SUMPRODUCT(N(RIGHT($D2,LEN(Sheet2!$A$1:$A$5))=Sheet2!$A$1:$A$5))>0)
Thanks, Detlef Lewin. I learn something new.
Then, I tried
=OR(RIGHT($D2,LEN({" Avenue"," Road"}))={" Avenue"," Road"})
This works too. This is not an array formula. However, if I want to make the search with dynamic keywords in cells, I still need to make it an array formula.
Willy, that's even better because CSE is not required. And it uses less functions and less operations.