Excel formula if

Hello,
can you please help,
I'm trying to say, if left of cell like then
=IF(LEFT(AK2,10)="RESIDENCE",AM2+3,IF(LEFT(AK2,11)="RESIDENTIAL",AM2+3,IF(LEFT(AK2,9)="RESIDENT",AM2+3,AM2)))

it seems to work only on exact words.
Example
if the cells was
RESIDENCE_____
RESIDENTIAL ---
RESIDENT yes

formula doesn't work.
thanks
W.E.BAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Subodh Tiwari (Neeraj)Connect With a Mentor Excel & VBA ExpertCommented:
Does column AK contains some leasing spaces in them?

BTW try this....

=IF(OR(LEFT(TRIM(AK2),8)="RESIDENT",LEFT(TRIM(AK2),9)="RESIDENCE",LEFT(TRIM(AK2),11)="RESIDENTIAL"),AM2+3,AM2+2)

Open in new window


If that doesn't work for you, please upload a sample workbook with some dummy data.
1
 
Naresh PatelConnect With a Mentor TraderCommented:
Try something like this or post sample Work book.

=IF(ISERROR(FIND("Resident",AK2,1))=TRUE,0,AM2+3)

Open in new window


Thanks
0
 
W.E.BAuthor Commented:
Thank you, this worked,
=IF(ISERROR(FIND("RESIDENCE",AK2,1))=TRUE,AM2,AM2+3)

how do I add to the formula
RESIDENTIAL
RESIDENT
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Or you can also try this....

=IF(OR(ISNUMBER(SEARCH({"Residence","Resident","Residential"},AK2))),AM2+3,AM2+2)

Open in new window

0
 
W.E.BAuthor Commented:
thank you very much.
0
 
W.E.BAuthor Commented:
thanks
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome.
0
 
Naresh PatelTraderCommented:
@Subodh Tiwari (Neeraj)...Thumbs Up ....this is how i learnt .....Thanks
@ Wass_QA  mine formula wont work for your all condition.
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
@itjockey

Glad you found it helpful. :)
0
 
Rob HensonFinance AnalystCommented:
Don't know if this would have helped but your original formula had incorrect character lengths for two of the options:

LEFT(AK2,10)="RESIDENCE"  but RESIDENCE = 9
LEFT(AK2,11)="RESIDENTIAL" correct as RESIDENTIAL = 11
LEFT(AK2,9)="RESIDENT" but RESIDENT = 8
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.