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 sincere apologies to Detlef Lewin. I'm a newbie with Excel and didn't realize I needed to use the Ctrl + Shift + Enter for his formula to work. My bad. Please forgive me.
Thanks for the heads up Willy Lau, and the non-array format to use with the IF statement.
Patrick
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?
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.