Skip to content
  • Try Smartsheet

    • Formulas and Functions

    Formulas and Functions

    Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

    If Contains lookup to another sheet result is empty show value from other cell in row?

    ✭✭✭✭
    in Formulas and Functions

    I have the following formula in a cell that until now was successfully looking up a date in another sheet and when the match of "OSHA Inspections" was true in that other sheet it would grab the most recent date for the shipto id. What I'm trying to do is modify the formula so that when the date field is blank in the lookup, it instead shows a date that is in a different column of the sheet where the formula is located. The below is still successfully looking up the date from the second sheet but, the cells are remaining blank when the lookup doesn't return a date. Any tips on how to get this working would be appreciated.

    =IF(CONTAINS("OSHA Inspections", {SVC Orders Range 8}), MAX(COLLECT({SVC Orders ORDER_DATE}, {SVC Orders SHIPTO}, [SHIPTO_ID]@row)), [Last Inspection Date]@row)

    · Share on FacebookShare on Twitter

    Best Answer

    • ✭✭✭✭
      Answer ✓

      This seems to be working.

      =IF(ISBLANK(CONTAINS("OSHA Inspections", {SVC Orders Range 8})), (MAX(COLLECT({SVC Orders ORDER_DATE}, {SVC Orders SHIPTO}, [SHIPTO_ID]@row))), [Last Inspection Date]@row)

      · Share on FacebookShare on Twitter

    Answers

    • Community Champion

      In English, your logic would just be that If (your original formula) = blank, then do another formula, otherwise, (your original formula). In Smartsheet, it'd look like below, just replace the «INSERT OTHER FORMULA HERE» with the lookup to that second date column.

      =IF(IF(CONTAINS("OSHA Inspections", {SVC Orders Range 8}), MAX(COLLECT({SVC Orders ORDER_DATE}, {SVC Orders SHIPTO}, [SHIPTO_ID]@row)), [Last Inspection Date]@row)="", <<INSERT OTHER FORMULA HERE>>, IF(CONTAINS("OSHA Inspections", {SVC Orders Range 8}), MAX(COLLECT({SVC Orders ORDER_DATE}, {SVC Orders SHIPTO}, [SHIPTO_ID]@row)), [Last Inspection Date]@row)

      Jason Tarpinian - Sevan Technology

      Smartsheet Aligned Partner

      · Share on FacebookShare on Twitter
    • ✭✭✭✭
      Answer ✓

      This seems to be working.

      =IF(ISBLANK(CONTAINS("OSHA Inspections", {SVC Orders Range 8})), (MAX(COLLECT({SVC Orders ORDER_DATE}, {SVC Orders SHIPTO}, [SHIPTO_ID]@row))), [Last Inspection Date]@row)

      · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Help Article Resources

    Create and edit formulas in Smartsheet

    Formula combinations for cross sheet references

    Smartsheet functions list

    Quick Links

    Categories

    Want to practice working with formulas directly in Smartsheet?

    Check out the Formula Handbook template!

    Trending in Formulas and Functions