Forum Discussion

Copper Contributor

Aug 05, 2024

Solved

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

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

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" ), "" )
- Jna3276
  Copper Contributor
  Aug 05, 2024
  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
  - Riny_van_Eekelen
    Platinum Contributor
    Aug 05, 2024
    Jna3276 Works on my system. Can't see anything different from my formula. Perhaps you see something different.