SOLVED

# Excel IF Formula Combination Help

Copper Contributor

# Excel IF Formula Combination Help

Hi,

I'd like to create a formula which looks at column A for a value containing 'MD' if it contains this then to go to column B AND C and if they both state 'Complete' then to return words in column D with 'Case Closed'

If column A does not contain 'MD' then return a blank and not to move to the if statement or next stage.

If one of the two Column B and C contain any other words then column D should return words 'Case Open'

Hope that all makes sense. Is this possible?

At the moment I have a formula which only looks at column B and C, this is the forumla with the actual column table headers:

=IF(AND([@[Servicing Status]]="Complete", [@[Complaint Status]]="Complete"), "Case closed", "Case Open")

5 Replies

# Re: Excel IF Formula Combination Help

That would be:

``=IF([@ColA]="MD",IF(AND([@[Servicing Status]]="Complete", [@[Complaint Status]]="Complete"), "Case closed", "Case Open"),"")``

Use the correct name for the column that should contain "MD".

# Re: Excel IF Formula Combination Help

Many thanks for this, this has worked, however the cell would contain other words, not just MD.

Would it be possible to amend the formula so it recognises if the cell contains MD, when the cell may say MD Complaint. I tried adding * either side of MD ("*MD*") but that hasn't worked

Alternatively, could I use this formula to check for multiple statements eg.MD Complaint, MD Escalation, MD Repeat Complaint etc?

best response confirmed by Jna3276 (Copper Contributor)
Solution

# Re: Excel IF Formula Combination Help

@Jna3276 Then it becomes a little bit of a monster

``````=IF(
IFERROR(FIND("MD", [@ColA]), 0),
IF(
AND(
[@[Servicing Status]] = "Complete",
[@[Complaint Status]] = "Complete"
),
"Case closed",
"Case Open"
),
""
)``````

# Re: Excel IF Formula Combination Help

@Riny_van_Eekelen Hi, this is coming up as an error with the formula, red outline box. Excel states, you've entered too many arguments for this arguement

# Re: Excel IF Formula Combination Help

@Jna3276 Works on my system. Can't see anything different from my formula. Perhaps you see something different.

1 best response

Accepted Solutions
best response confirmed by Jna3276 (Copper Contributor)
Solution

# Re: Excel IF Formula Combination Help

@Jna3276 Then it becomes a little bit of a monster

``````=IF(
IFERROR(FIND("MD", [@ColA]), 0),
IF(
AND(
[@[Servicing Status]] = "Complete",
[@[Complaint Status]] = "Complete"
),
"Case closed",
"Case Open"
),
""
)``````