Link to home
Start Free TrialLog in
Avatar of Bill Golden
Bill GoldenFlag for United States of America

asked on

Compound IF statement in Excel

I need to test the existence of an X in various cells in another worksheet.  

If 'Balance Sheet'!P221 contains an X, then return the value of 'Balance Sheet!B288 + 1, but if P221 is blank...
If 'Balance Sheet'!P148 contains an X, then return the value of 'Balance Sheet!B215 + 1, but if P148 is blank...
If 'Balance Sheet'!P75 contains an X, then return the value of 'Balance Sheet!B142 + 1, but if P75 is blank...
If 'Balance Sheet'!P2 contains an X, then return the value of 'Balance Sheet!B69 + 1, but if P2 is blank...
return the value of Setup!D53
ASKER CERTIFIED SOLUTION
Avatar of Rory Archibald
Rory Archibald
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
The basic pattern you're after is as follows, modelled on the first case of your question.
=IF(IFERROR(SEARCH("X",'Balance Sheet'!P221),0)>0,'Balance Sheet'!B288 + 1, "P211 is blank")

Open in new window


A couple of clarifications: are you looking to nest all of your statements above, i.e. the second case applies if P221 is blank and so on for the other cases?

Does the contains check need to be case sensitive? SEARCH, which I have used above is not case sensitive. You can use FIND as a direct replacement if the check does need to be case sensitive.
Avatar of Bill Golden

ASKER

That got the job done.  There was a " ' " or two missing after the Balance Sheet reference but the previous answer allowed me to spot the problem.  Thanks.

Bill