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

cbsarge

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)

cbsarge

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)

Jason Tarpinian

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)

cbsarge

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)