Combining Nested If and Switch Formulas

Question

I'm not sure if what I'm trying to do is possible, but here goes:

I'm working off of an existing base template, and am trying to tweak one of the formulas that's in there. The existing formula is as follows:

I would like to add one additional condition: if Equipment Status is "Unknown" the result is

"❌ Not Available". Since this is pulling from a different field than the existing SWITCH formula, I can't add it in there; I've tried nesting another "If" within this formula, but that also doesn't seem to work. It may be that I simply can't do this, but if anyone has any ideas or suggestions, I'd appreciate it--I'm pretty new to Airtable formulas, and trying to figure out how best to leverage these formulas. Thanks in advance!

Ben_Young1 · Accepted Answer

Hey @Eliz_Comm!

Here's the original formula from your screenshot:

IF(
    OR(
        {Equipment Status} = "Out of Commission",
        {Equipment Status} = "Out for Repair",
    ),
    "⚫ Removed",
    SWITCH(
        {Status},
        "Checked In",
            "✔ Available",
        "Checked Out",
            "❌ Not Available",
        "✔ Available"
    )
)

In plain language, this formula looks at whether the Equipment Status field returns the value of "Out of Commission" or "Out for Repair".
If that expression evaluates true, the value of "⚫ Removed" is returned.
If that expression evaluates false, we default to a SWITCH function that evaluates the Status field values with a fallback value of "✔ Available."

Now, you'd like to add a conditional spike to this formula where the Equipment Status field is "Unknown", the formula should return "❌ Not Available."
There are a couple of possible permutations to this edit, but here's one way to do this:

IF(
    OR(
        {Equipment Status} = "Out of Commission",
        {Equipment Status} = "Out for Repair",
    ),
    "⚫ Removed",
    IF(
        {Equipment Status} = "Unknown",
        "❌ Not Available",
        SWITCH(
            {Status},
            "Checked In",
                "✔ Available",
            "Checked Out",
                "❌ Not Available",
            "✔ Available"
        )
    )
)

This worked perfectly--thank you so much!

Eliz_Comm · Answer

Hey @Eliz_Comm!

Here's the original formula from your screenshot:

IF(
    OR(
        {Equipment Status} = "Out of Commission",
        {Equipment Status} = "Out for Repair",
    ),
    "⚫ Removed",
    SWITCH(
        {Status},
        "Checked In",
            "✔ Available",
        "Checked Out",
            "❌ Not Available",
        "✔ Available"
    )
)

In plain language, this formula looks at whether the Equipment Status field returns the value of "Out of Commission" or "Out for Repair".
If that expression evaluates true, the value of "⚫ Removed" is returned.
If that expression evaluates false, we default to a SWITCH function that evaluates the Status field values with a fallback value of "✔ Available."

Now, you'd like to add a conditional spike to this formula where the Equipment Status field is "Unknown", the formula should return "❌ Not Available."
There are a couple of possible permutations to this edit, but here's one way to do this:

IF(
    OR(
        {Equipment Status} = "Out of Commission",
        {Equipment Status} = "Out for Repair",
    ),
    "⚫ Removed",
    IF(
        {Equipment Status} = "Unknown",
        "❌ Not Available",
        SWITCH(
            {Status},
            "Checked In",
                "✔ Available",
            "Checked Out",
                "❌ Not Available",
            "✔ Available"
        )
    )
)

This worked perfectly--thank you so much!

Sign up

Login to the community

Scanning file for viruses.

This file cannot be downloaded