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