Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Excel formula if

Posted on 2016-07-17
Medium Priority
83 Views
Hello,
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
0
Question by:W.E.B
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 4
• 3
• 2
• +1

LVL 8

Assisted Solution

Naresh Patel earned 1000 total points
ID: 41715981
Try something like this or post sample Work book.

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

Thanks
0

Author Comment

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

how do I add to the formula
RESIDENTIAL
RESIDENT
0

LVL 32

Accepted Solution

Subodh Tiwari (Neeraj) earned 1000 total points
ID: 41715984
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)
``````

If that doesn't work for you, please upload a sample workbook with some dummy data.
1

LVL 32

Expert Comment

ID: 41715987
Or you can also try this....

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

Author Comment

ID: 41715988
thank you very much.
0

Author Closing Comment

ID: 41715990
thanks
0

LVL 32

Expert Comment

ID: 41715991
You're welcome.
0

LVL 8

Expert Comment

ID: 41715992
@Subodh Tiwari (Neeraj)...Thumbs Up ....this is how i learnt .....Thanks
@ Wass_QA  mine formula wont work for your all condition.
0

LVL 32

Expert Comment

ID: 41715993
@itjockey

0

LVL 33

Expert Comment

ID: 41717402
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

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
###### Suggested Courses
Course of the Month6 days, 14 hours left to enroll