=IF(AND(OR(HAS Column Formula with 3 results (option 1, option 2, both) #INCORRECT ARGUMENT SET

Gabrielle Mejia

I have a search range which is a column that has up to 35 bus routes. The bus routes are operated out of one of two facilities, "ARCADIA" and "POMONA". I am trying to create a column formula that will return a result of "ARCADIA", "POMONA", or "BOTH".

I can successfully create a column formula that will return one of those results. But when I try to combine them, I get an error: #INCORRECT ARGUMENT SET. I don't know why! Can anyone help identify what I'm doing wrong?

Simplified, I'm using =IF(AND(OR(HAS(…))), (OR(HAS(…))), "BOTH", IF(OR(HAS(…)), "POMONA", IF(OR(HAS(…)), "ARCADIA")))

=IF(AND(OR(HAS([IMPACTED ROUTE]@row , "486"), HAS([IMPACTED ROUTE]@row , "195"), HAS([IMPACTED ROUTE]@row , "197"), HAS([IMPACTED ROUTE]@row , "282"), HAS([IMPACTED ROUTE]@row , "285"), HAS([IMPACTED ROUTE]@row , "286"), HAS([IMPACTED ROUTE]@row , "289"), HAS([IMPACTED ROUTE]@row , "291"), HAS([IMPACTED ROUTE]@row , "292"), HAS([IMPACTED ROUTE]@row , "482"), HAS([IMPACTED ROUTE]@row , "488"), HAS([IMPACTED ROUTE]@row , "490"), HAS([IMPACTED ROUTE]@row , "492"), HAS([IMPACTED ROUTE]@row , "495"), HAS([IMPACTED ROUTE]@row , "499"), HAS([IMPACTED ROUTE]@row , "699"), HAS([IMPACTED ROUTE]@row , "853"))), (OR(HAS([IMPACTED ROUTE]@row , "178"), HAS([IMPACTED ROUTE]@row , "185"), HAS([IMPACTED ROUTE]@row , "187"), HAS([IMPACTED ROUTE]@row , "188"), HAS([IMPACTED ROUTE]@row , "190"), HAS([IMPACTED ROUTE]@row , "194"), HAS([IMPACTED ROUTE]@row , "269"), HAS([IMPACTED ROUTE]@row , "270"), HAS([IMPACTED ROUTE]@row , "272"), HAS([IMPACTED ROUTE]@row , "274"), HAS([IMPACTED ROUTE]@row , "280"), HAS([IMPACTED ROUTE]@row , "281"), HAS([IMPACTED ROUTE]@row , "284"), HAS([IMPACTED ROUTE]@row , "480"), HAS([IMPACTED ROUTE]@row , "493"), HAS([IMPACTED ROUTE]@row , "498"), HAS([IMPACTED ROUTE]@row , "861"), HAS([IMPACTED ROUTE]@row , "Silver Streak"))), "BOTH", IF(OR(HAS([IMPACTED ROUTE]@row , "486"), HAS([IMPACTED ROUTE]@row , "195"), HAS([IMPACTED ROUTE]@row , "197"), HAS([IMPACTED ROUTE]@row , "282"), HAS([IMPACTED ROUTE]@row , "285"), HAS([IMPACTED ROUTE]@row , "286"), HAS([IMPACTED ROUTE]@row , "289"), HAS([IMPACTED ROUTE]@row , "291"), HAS([IMPACTED ROUTE]@row , "292"), HAS([IMPACTED ROUTE]@row , "482"), HAS([IMPACTED ROUTE]@row , "488"), HAS([IMPACTED ROUTE]@row , "490"), HAS([IMPACTED ROUTE]@row , "492"), HAS([IMPACTED ROUTE]@row , "495"), HAS([IMPACTED ROUTE]@row , "499"), HAS([IMPACTED ROUTE]@row , "699"), HAS([IMPACTED ROUTE]@row , "853")), "POMONA", IF(OR(HAS([IMPACTED ROUTE]@row , "178"), HAS([IMPACTED ROUTE]@row , "185"), HAS([IMPACTED ROUTE]@row , "187"), HAS([IMPACTED ROUTE]@row , "188"), HAS([IMPACTED ROUTE]@row , "190"), HAS([IMPACTED ROUTE]@row , "194"), HAS([IMPACTED ROUTE]@row , "269"), HAS([IMPACTED ROUTE]@row , "270"), HAS([IMPACTED ROUTE]@row , "272"), HAS([IMPACTED ROUTE]@row , "274"), HAS([IMPACTED ROUTE]@row , "280"), HAS([IMPACTED ROUTE]@row , "281"), HAS([IMPACTED ROUTE]@row , "284"), HAS([IMPACTED ROUTE]@row , "480"), HAS([IMPACTED ROUTE]@row , "493"), HAS([IMPACTED ROUTE]@row , "498"), HAS([IMPACTED ROUTE]@row , "861"), HAS([IMPACTED ROUTE]@row , "Silver Streak")), "ARCADIA")))

jmyzk_cloudsmart_jp

Hi @Gabrielle Mejia

You're absolutely right that your original formula was nearly correct, but the error #INCORRECT ARGUMENT SET comes from a common issue: the final IF() function is missing the required value_if_false argument.

In short, where your formula is in the IF(A, B) format, you need to use the IF(A, B, C) format in the last IF clause.

IF(OR( ,,,, HAS([IMPACTED ROUTE]@row , "Silver Streak")) // end of OR

"ARCADIA")

IF() in Smartsheet must follow the structure:

IF(condition, value_if_true, value_if_false)

In your original formula, the last IF() ends with "ARCADIA" but doesn’t include what to return if none of the conditions are met — so Smartsheet throws an error.

Here's the corrected version of your formula with "" added as the fallback:

=IF(

  AND(

    OR(HAS(...POMONA routes...)),

    OR(HAS(...ARCADIA routes...))

  ),

  "BOTH",

  IF(

    OR(HAS(...POMONA routes...)),

    "POMONA",

    IF(

      OR(HAS(...ARCADIA routes...)),

      "ARCADIA",
 ""  // ← This missing value_if_false is what caused the error

    )

  )

)

You can substitute the actual HAS(...) values as you had originally.

https://app.smartsheet.com/b/publish?EQBCT=45a8098481e744138d5672b8dbfd60ad

Fixed Formula

=IF(AND(OR(HAS([Impacted Route]@row , "486"), HAS([Impacted Route]@row , "195"), HAS([Impacted Route]@row , "197"), HAS([Impacted Route]@row , "282"), HAS([Impacted Route]@row , "285"), HAS([Impacted Route]@row , "286"), HAS([Impacted Route]@row , "289"), HAS([Impacted Route]@row , "291"), HAS([Impacted Route]@row , "292"), HAS([Impacted Route]@row , "482"), HAS([Impacted Route]@row , "488"), HAS([Impacted Route]@row , "490"), HAS([Impacted Route]@row , "492"), HAS([Impacted Route]@row , "495"), HAS([Impacted Route]@row , "499"), HAS([Impacted Route]@row , "699"), HAS([Impacted Route]@row , "853")), OR(HAS([Impacted Route]@row , "178"), HAS([Impacted Route]@row , "185"), HAS([Impacted Route]@row , "187"), HAS([Impacted Route]@row , "188"), HAS([Impacted Route]@row , "190"), HAS([Impacted Route]@row , "194"), HAS([Impacted Route]@row , "269"), HAS([Impacted Route]@row , "270"), HAS([Impacted Route]@row , "272"), HAS([Impacted Route]@row , "274"), HAS([Impacted Route]@row , "280"), HAS([Impacted Route]@row , "281"), HAS([Impacted Route]@row , "284"), HAS([Impacted Route]@row , "480"), HAS([Impacted Route]@row , "493"), HAS([Impacted Route]@row , "498"), HAS([Impacted Route]@row , "861"), HAS([Impacted Route]@row , "Silver Streak"))), "BOTH", IF(OR(HAS([Impacted Route]@row , "486"), HAS([Impacted Route]@row , "195"), HAS([Impacted Route]@row , "197"), HAS([Impacted Route]@row , "282"), HAS([Impacted Route]@row , "285"), HAS([Impacted Route]@row , "286"), HAS([Impacted Route]@row , "289"), HAS([Impacted Route]@row , "291"), HAS([Impacted Route]@row , "292"), HAS([Impacted Route]@row , "482"), HAS([Impacted Route]@row , "488"), HAS([Impacted Route]@row , "490"), HAS([Impacted Route]@row , "492"), HAS([Impacted Route]@row , "495"), HAS([Impacted Route]@row , "499"), HAS([Impacted Route]@row , "699"), HAS([Impacted Route]@row , "853")), "POMONA", IF(OR(HAS([Impacted Route]@row , "178"), HAS([Impacted Route]@row , "185"), HAS([Impacted Route]@row , "187"), HAS([Impacted Route]@row , "188"), HAS([Impacted Route]@row , "190"), HAS([Impacted Route]@row , "194"), HAS([Impacted Route]@row , "269"), HAS([Impacted Route]@row , "270"), HAS([Impacted Route]@row , "272"), HAS([Impacted Route]@row , "274"), HAS([Impacted Route]@row , "280"), HAS([Impacted Route]@row , "281"), HAS([Impacted Route]@row , "284"), HAS([Impacted Route]@row , "480"), HAS([Impacted Route]@row , "493"), HAS([Impacted Route]@row , "498"), HAS([Impacted Route]@row , "861"), HAS([Impacted Route]@row , "Silver Streak")), "ARCADIA", "")))

or

=IF(

  AND(

    OR(

      HAS([IMPACTED ROUTE]@row, "486"), HAS([IMPACTED ROUTE]@row, "195"), HAS([IMPACTED ROUTE]@row, "197"),

      HAS([IMPACTED ROUTE]@row, "282"), HAS([IMPACTED ROUTE]@row, "285"), HAS([IMPACTED ROUTE]@row, "286"),

      HAS([IMPACTED ROUTE]@row, "289"), HAS([IMPACTED ROUTE]@row, "291"), HAS([IMPACTED ROUTE]@row, "292"),

      HAS([IMPACTED ROUTE]@row, "482"), HAS([IMPACTED ROUTE]@row, "488"), HAS([IMPACTED ROUTE]@row, "490"),

      HAS([IMPACTED ROUTE]@row, "492"), HAS([IMPACTED ROUTE]@row, "495"), HAS([IMPACTED ROUTE]@row, "499"),

      HAS([IMPACTED ROUTE]@row, "699"), HAS([IMPACTED ROUTE]@row, "853")

    ),

    OR(

      HAS([IMPACTED ROUTE]@row, "178"), HAS([IMPACTED ROUTE]@row, "185"), HAS([IMPACTED ROUTE]@row, "187"),

      HAS([IMPACTED ROUTE]@row, "188"), HAS([IMPACTED ROUTE]@row, "190"), HAS([IMPACTED ROUTE]@row, "194"),

      HAS([IMPACTED ROUTE]@row, "269"), HAS([IMPACTED ROUTE]@row, "270"), HAS([IMPACTED ROUTE]@row, "272"),

      HAS([IMPACTED ROUTE]@row, "274"), HAS([IMPACTED ROUTE]@row, "280"), HAS([IMPACTED ROUTE]@row, "281"),

      HAS([IMPACTED ROUTE]@row, "284"), HAS([IMPACTED ROUTE]@row, "480"), HAS([IMPACTED ROUTE]@row, "493"),

      HAS([IMPACTED ROUTE]@row, "498"), HAS([IMPACTED ROUTE]@row, "861"), HAS([IMPACTED ROUTE]@row, "Silver Streak")

    )

  ),

  "BOTH",

  IF(

    OR(

      HAS([IMPACTED ROUTE]@row, "486"), HAS([IMPACTED ROUTE]@row, "195"), HAS([IMPACTED ROUTE]@row, "197"),

      HAS([IMPACTED ROUTE]@row, "282"), HAS([IMPACTED ROUTE]@row, "285"), HAS([IMPACTED ROUTE]@row, "286"),

      HAS([IMPACTED ROUTE]@row, "289"), HAS([IMPACTED ROUTE]@row, "291"), HAS([IMPACTED ROUTE]@row, "292"),

      HAS([IMPACTED ROUTE]@row, "482"), HAS([IMPACTED ROUTE]@row, "488"), HAS([IMPACTED ROUTE]@row, "490"),

      HAS([IMPACTED ROUTE]@row, "492"), HAS([IMPACTED ROUTE]@row, "495"), HAS([IMPACTED ROUTE]@row, "499"),

      HAS([IMPACTED ROUTE]@row, "699"), HAS([IMPACTED ROUTE]@row, "853")

    ),

    "POMONA",

    IF(

      OR(

        HAS([IMPACTED ROUTE]@row, "178"), HAS([IMPACTED ROUTE]@row, "185"), HAS([IMPACTED ROUTE]@row, "187"),

        HAS([IMPACTED ROUTE]@row, "188"), HAS([IMPACTED ROUTE]@row, "190"), HAS([IMPACTED ROUTE]@row, "194"),

        HAS([IMPACTED ROUTE]@row, "269"), HAS([IMPACTED ROUTE]@row, "270"), HAS([IMPACTED ROUTE]@row, "272"),

        HAS([IMPACTED ROUTE]@row, "274"), HAS([IMPACTED ROUTE]@row, "280"), HAS([IMPACTED ROUTE]@row, "281"),

        HAS([IMPACTED ROUTE]@row, "284"), HAS([IMPACTED ROUTE]@row, "480"), HAS([IMPACTED ROUTE]@row, "493"),

        HAS([IMPACTED ROUTE]@row, "498"), HAS([IMPACTED ROUTE]@row, "861"), HAS([IMPACTED ROUTE]@row, "Silver Streak")

      ),

      "ARCADIA",

      ""  // ✅ This part was missing in the original formula

    )

  )

)

Alternatively, you can skip the nested logic and use a mapping sheet (as shown at the bottom) approach, which is easier to maintain:

[Impacted Facility with Mapping Sheet] =JOIN( DISTINCT( COLLECT( {Route to Facility Mapping : Facility}, {Route to Facility Mapping : Facility}, ISTEXT(@cell ), {Route to Facility Mapping : Route}, HAS([IMPACTED ROUTE]@row , @cell ) ) ), CHAR(10))

This will return "POMONA", "ARCADIA", or both if the row includes mixed routes, and it avoids the need to hardcode route numbers in your formula.

Hope this helps!

https://app.smartsheet.com/b/publish?EQBCT=c4da1118ae524b12a531b54138b94683