Excel IF Formula Combination Help

[Jna3276]

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")

[#2]

@Jna3276 

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".

[#3]

@Riny_van_Eekelen 

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?

[#4 · Accepted solution]

@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"
    ),
    ""
)

[#5]

@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

 

[#6]

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