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

excel

Formulas and Functions

Riny_van_Eekelen

Aug 05, 2024

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 Replies

Riny_van_Eekelen
Platinum Contributor
Aug 05, 2024
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".
- Jna3276
  Copper Contributor
  Aug 05, 2024
  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?
  - Riny_van_Eekelen
    Platinum Contributor
    Aug 05, 2024
    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" ), "" )

Forum Discussion

Excel IF Formula Combination Help

5 Replies

Resources