Assistance Needed with Formula

Rohammad

I am reaching out to seek assistance with a complex formula that I am trying to implement in Smartsheet. My goal is to pull specific fleet codes based on values found in multiple columns, and default to a third-party value if no match is found.

Requirements:
1. **Check `SMN_SMLR_AC_TYP_CD` Column**: Search for specific fleet codes in the `SMN_SMLR_AC_TYP_CD` column.
2. **Check `Comment 1` Column**: If no match is found in the `SMN_SMLR_AC_TYP_CD` column, search for specific fleet codes in the `Comment 1` column.
3. **Fallback**: If no match is found in both columns, use the value from the `Third Party` column.

The Formula
Here is the formula I am attempting to use:

=IF(OR(ISNUMBER(FIND("88", [SMN_SMLR_AC_TYP_CD]@row)), ISNUMBER(FIND("67", [SMN_SMLR_AC_TYP_CD]@row)), ISNUMBER(FIND("73", [SMN_SMLR_AC_TYP_CD]@row)), ISNUMBER(FIND("77", [SMN_SMLR_AC_TYP_CD]@row)), ISNUMBER(FIND("57", [SMN_SMLR_AC_TYP_CD]@row)), ISNUMBER(FIND("37", [SMN_SMLR_AC_TYP_CD]@row)), ISNUMBER(FIND("73N", [SMN_SMLR_AC_TYP_CD]@row)), ISNUMBER(FIND("87", [SMN_SMLR_AC_TYP_CD]@row)), ISNUMBER(FIND("47", [SMN_SMLR_AC_TYP_CD]@row)), ISNUMBER(FIND("17", [SMN_SMLR_AC_TYP_CD]@row)), ISNUMBER(FIND("32", [SMN_SMLR_AC_TYP_CD]@row)), ISNUMBER(FIND("35", [SMN_SMLR_AC_TYP_CD]@row)), ISNUMBER(FIND("765", [SMN_SMLR_AC_TYP_CD]@row)), ISNUMBER(FIND("220", [SMN_SMLR_AC_TYP_CD]@row)), ISNUMBER(FIND("330", [SMN_SMLR_AC_TYP_CD]@row))), IF(ISNUMBER(FIND("88", [SMN_SMLR_AC_TYP_CD]@row)), "M88", IF(ISNUMBER(FIND("67", [SMN_SMLR_AC_TYP_CD]@row)), "767", IF(ISNUMBER(FIND("73", [SMN_SMLR_AC_TYP_CD]@row)), "737", IF(ISNUMBER(FIND("77", [SMN_SMLR_AC_TYP_CD]@row)), "777", IF(ISNUMBER(FIND("57", [SMN_SMLR_AC_TYP_CD]@row)), "757", IF(ISNUMBER(FIND("37", [SMN_SMLR_AC_TYP_CD]@row)), "737", IF(ISNUMBER(FIND("73N", [SMN_SMLR_AC_TYP_CD]@row)), "737", IF(ISNUMBER(FIND("87", [SMN_SMLR_AC_TYP_CD]@row)), "787", IF(ISNUMBER(FIND("47", [SMN_SMLR_AC_TYP_CD]@row)), "747", IF(ISNUMBER(FIND("17", [SMN_SMLR_AC_TYP_CD]@row)), "717", IF(ISNUMBER(FIND("32", [SMN_SMLR_AC_TYP_CD]@row)), "320", IF(ISNUMBER(FIND("35", [SMN_SMLR_AC_TYP_CD]@row)), "350", IF(ISNUMBER(FIND("765", [SMN_SMLR_AC_TYP_CD]@row)), "767", IF(ISNUMBER(FIND("220", [SMN_SMLR_AC_TYP_CD]@row)), "220", IF(ISNUMBER(FIND("330", [SMN_SMLR_AC_TYP_CD]@row)), "330", IF(OR(ISNUMBER(FIND("/88", [Comment 1]@row)), ISNUMBER(FIND("/67", [Comment 1]@row)), ISNUMBER(FIND("/73", [Comment 1]@row)), ISNUMBER(FIND("/77", [Comment 1]@row)), ISNUMBER(FIND("/57", [Comment 1]@row)), ISNUMBER(FIND("/37", [Comment 1]@row)), ISNUMBER(FIND("/73N", [Comment 1]@row)), ISNUMBER(FIND("/87", [Comment 1]@row))), IF(ISNUMBER(FIND("/88", [Comment 1]@row)), "M88", IF(ISNUMBER(FIND("/67", [Comment 1]@row)), "767", IF(ISNUMBER(FIND("/73", [Comment 1]@row)), "737", IF(ISNUMBER(FIND("/77", [Comment 1]@row)), "777", IF(ISNUMBER(FIND("/57", [Comment 1]@row)), "757", IF(ISNUMBER(FIND("/37", [Comment 1]@row)), "737", IF(ISNUMBER(FIND("/73N", [Comment 1]@row)), "737", IF(ISNUMBER(FIND("/87", [Comment 1]@row)), "787", [Third Party]@row)))))))), [Third Party]@row)))))))))))))))), [Third Party]@row))
```

Despite multiple attempts, I have been unable to get this formula to work as expected. Could you please review the formula and provide guidance on how to correct any errors or suggest an alternative approach that meets the stated requirements?

Thank you for your assistance.

Aman Shah

To fix the formula in Smartsheet, you need to streamline the logic for checking multiple columns and defaulting to a fallback value. Here's an optimized version of the formula. Hope this helps:

=IF(
  OR(
    ISNUMBER(FIND("88", [SMN_SMLR_AC_TYP_CD]@row)), 
    ISNUMBER(FIND("67", [SMN_SMLR_AC_TYP_CD]@row)), 
    ISNUMBER(FIND("73", [SMN_SMLR_AC_TYP_CD]@row)), 
    ISNUMBER(FIND("77", [SMN_SMLR_AC_TYP_CD]@row)), 
    ISNUMBER(FIND("57", [SMN_SMLR_AC_TYP_CD]@row)), 
    ISNUMBER(FIND("37", [SMN_SMLR_AC_TYP_CD]@row)), 
    ISNUMBER(FIND("73N", [SMN_SMLR_AC_TYP_CD]@row)), 
    ISNUMBER(FIND("87", [SMN_SMLR_AC_TYP_CD]@row)), 
    ISNUMBER(FIND("47", [SMN_SMLR_AC_TYP_CD]@row)), 
    ISNUMBER(FIND("17", [SMN_SMLR_AC_TYP_CD]@row)), 
    ISNUMBER(FIND("32", [SMN_SMLR_AC_TYP_CD]@row)), 
    ISNUMBER(FIND("35", [SMN_SMLR_AC_TYP_CD]@row)), 
    ISNUMBER(FIND("765", [SMN_SMLR_AC_TYP_CD]@row)), 
    ISNUMBER(FIND("220", [SMN_SMLR_AC_TYP_CD]@row)), 
    ISNUMBER(FIND("330", [SMN_SMLR_AC_TYP_CD]@row))
  ), 
  IF(
    ISNUMBER(FIND("88", [SMN_SMLR_AC_TYP_CD]@row)), "M88", 
    IF(
      ISNUMBER(FIND("67", [SMN_SMLR_AC_TYP_CD]@row)), "767", 
      IF(
        ISNUMBER(FIND("73", [SMN_SMLR_AC_TYP_CD]@row)), "737", 
        IF(
          ISNUMBER(FIND("77", [SMN_SMLR_AC_TYP_CD]@row)), "777", 
          IF(
            ISNUMBER(FIND("57", [SMN_SMLR_AC_TYP_CD]@row)), "757", 
            IF(
              ISNUMBER(FIND("37", [SMN_SMLR_AC_TYP_CD]@row)), "737", 
              IF(
                ISNUMBER(FIND("73N", [SMN_SMLR_AC_TYP_CD]@row)), "737", 
                IF(
                  ISNUMBER(FIND("87", [SMN_SMLR_AC_TYP_CD]@row)), "787", 
                  IF(
                    ISNUMBER(FIND("47", [SMN_SMLR_AC_TYP_CD]@row)), "747", 
                    IF(
                      ISNUMBER(FIND("17", [SMN_SMLR_AC_TYP_CD]@row)), "717", 
                      IF(
                        ISNUMBER(FIND("32", [SMN_SMLR_AC_TYP_CD]@row)), "320", 
                        IF(
                          ISNUMBER(FIND("35", [SMN_SMLR_AC_TYP_CD]@row)), "350", 
                          IF(
                            ISNUMBER(FIND("765", [SMN_SMLR_AC_TYP_CD]@row)), "767", 
                            IF(
                              ISNUMBER(FIND("220", [SMN_SMLR_AC_TYP_CD]@row)), "220", 
                              IF(
                                ISNUMBER(FIND("330", [SMN_SMLR_AC_TYP_CD]@row)), "330", 
                                IF(
                                  OR(
                                    ISNUMBER(FIND("/88", [Comment 1]@row)), 
                                    ISNUMBER(FIND("/67", [Comment 1]@row)), 
                                    ISNUMBER(FIND("/73", [Comment 1]@row)), 
                                    ISNUMBER(FIND("/77", [Comment 1]@row)), 
                                    ISNUMBER(FIND("/57", [Comment 1]@row)), 
                                    ISNUMBER(FIND("/37", [Comment 1]@row)), 
                                    ISNUMBER(FIND("/73N", [Comment 1]@row)), 
                                    ISNUMBER(FIND("/87", [Comment 1]@row))
                                  ), 
                                  IF(
                                    ISNUMBER(FIND("/88", [Comment 1]@row)), "M88", 
                                    IF(
                                      ISNUMBER(FIND("/67", [Comment 1]@row)), "767", 
                                      IF(
                                        ISNUMBER(FIND("/73", [Comment 1]@row)), "737", 
                                        IF(
                                          ISNUMBER(FIND("/77", [Comment 1]@row)), "777", 
                                          IF(
                                            ISNUMBER(FIND("/57", [Comment 1]@row)), "757", 
                                            IF(
                                              ISNUMBER(FIND("/37", [Comment 1]@row)), "737", 
                                              IF(
                                                ISNUMBER(FIND("/73N", [Comment 1]@row)), "737", 
                                                IF(
                                                  ISNUMBER(FIND("/87", [Comment 1]@row)), "787", 
                                                  [Third Party]@row
                                                )
                                              )
                                            )
                                          )
                                        )
                                      )
                                    )
                                  ), 
                                  [Third Party]@row
                                )
                              )
                            )
                          )
                        )
                      )
                    )
                  )
                )
              )
            )
          )
        )
      )
    )
  ), 
  [Third Party]@row
)

This formula first checks the SMN_SMLR_AC_TYP_CD column for any of the specified fleet codes and assigns the corresponding fleet code if found. If not found, it then checks the Comment 1 column for any of the specified fleet codes and assigns the corresponding fleet code if found. If no matches are found in both columns, it defaults to the value in the Third Party column. This approach simplifies and ensures the logic flow is clear and accurate.