Excel formula if

W.E.B
W.E.B used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Naresh PatelFinancial Adviser
Commented:
Try something like this or post sample Work book.

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

Open in new window


Thanks

Author

Commented:
Thank you, this worked,
=IF(ISERROR(FIND("RESIDENCE",AK2,1))=TRUE,AM2,AM2+3)

how do I add to the formula
RESIDENTIAL
RESIDENT
Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented:
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.
Rowby Goren Makes an Impact on Screen and Online

Learn about longtime user Rowby Goren and his great contributions to the site. We explore his method for posing questions that are likely to yield a solution, and take a look at how his career transformed from a Hollywood writer to a website entrepreneur.

Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
Or you can also try this....

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

Open in new window

Author

Commented:
thank you very much.

Author

Commented:
thanks
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
You're welcome.
Naresh PatelFinancial Adviser

Commented:
@Subodh Tiwari (Neeraj)...Thumbs Up ....this is how i learnt .....Thanks
@ Wass_QA  mine formula wont work for your all condition.
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
@itjockey

Glad you found it helpful. :)
Rob HensonFinance Analyst

Commented:
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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial