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)
