IF contains nested formula help

[Addie_Dunham]

We have a system for reviewing new tools where they are checked for meeting accessibility standards and security standards. These are two separate teams with different workflows. The integration team needs to know when a tool review is considered complete.

I have two columns; one for the accessibility phase, one for security phase. I'm trying to make a formula for overall review status. There are multiple decision statuses for each. I would like to make the formula display either "Awaiting Decision" or "Review Complete". Review Complete would show if both columns have a value that indicates a decision was made.

For example, for tool A, the accessibility status = Testing Not Required and security status = Complete, it should be marked Review Completed in the formula column.

For tool B, the accessibility status = Completed and security status = Intake, it should be marked as "Awaiting Decision".

I've tried a variety of IF(FIND( arrangements but I can't seem to get it right. I only saved the most recent attempt, which was more simplified than what I'd like, but I'm not sure where I went awry.

Any help would be extremely appreciated.

IF(
  FIND(
    'Completed',
    {Accessibility Status Phase}, 
    IF(
      FIND(
        'Completed',
        {Security Campus Phase},
        ),
      'Review Completed',
      )
      'Awaiting Decision'
    )
   'Awaiting Decision'
)

[Ben_Young1 · Accepted solution]

Hey @Addie_Dunham! 

Here are two different formulas that should get you what you're looking for based on the details you've provided:

IF(
    AND(
        {Accessibility Status Phase} = "Completed",
        {Security Campus Phase} = "Completed"
    ),
    "Review Completed",
    IF(
        OR(
            {Accessibility Status Phase},
            {Security Campus Phase}
        ),
        "Awaiting Decision"
    )
)

IF(
    AND(
        FIND(
            "Completed",
            {Accessibility Status Phase}
        ),
        FIND(
            "Completed",
            {Security Campus Phase}
        )
    ),
    "Review Completed",
    IF(
        OR(
            {Accessibility Status Phase},
            {Security Campus Phase}
        ),
        "Awaiting Decision"
    )
)

[kuovonne]

The formula that you posted has parentheses in the wrong places and is missing commas.

Since your fields are single selects, you should be able to use = instead of FIND(), which might make things simpler. You can also consider using SWITCH().

[Addie_Dunham]

Thank you so much !