Forum Discussion
Need an Excel Formula giving True or False based on the last word in the cell
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)
My approach is based on Detlef Lewin's solution, and if you want to cater Circle, Trail, etc. my approach still need to use array formula.
Says, currently in Sheet1, you can create Sheet2. Type, Circle, Avenue, Road, Trail, etc, in A1 To A4 (or more as you wish), Then, in sheet1 D2
{=IF($D2="","",OR(RIGHT($D2,LEN(Sheet2!$A$1:$A$4))=Sheet2!$A$1:$A$4))}
Will it work if the array is in rows instead of columns?
For example:
{=IF($D2="","",OR(RIGHT($D2,LEN(Sheet2!$A$1:$A$200))=Sheet2!$A$1:$A$200))}
One more thing that I forgot to mention, my approach is case-sensitive, but Detlef Lewin's solution is not, which is much better to be used on your case.
Thanks for the heads-up on the case. I'll run a =PROPER( ) to standardize it.
Your formula:
{=IF($A2="","",OR(RIGHT($A2,LEN(Sheet2!$A$1:$A$5))=Sheet2!$A$1:$A$5))}
Using an array in Sheet2 seems the best solution.
My list has thousands of rows. Is there an easy way to copy array formulas across multiple cells?
"Is there an easy way to copy array formulas across multiple cells?"
Enter the formula in the first cell with CSE and then copy it across.
If you have to edit the formula later then you have to enter it with CSE again.
CSE = CTRL-SHIFT-ENTER
As what you type, it works on D3, D4, D5...etc, but not A2, B2, O2 or other columns in row 2 because $D is using.
