LOOKUP FUNCTION - Check if date falls within a range and return a corresponding value (same row)

I've gotten this to work in Excel but I'm having trouble in SmartSheets. Essentially I'm trying to create a row in my Smartsheet that returns a "submit by" date based on the date the user enters in the adjacent cell. In order to d this in Excel I created a sheet with the period start date in column A, the period end date in column B and the 'submit by' date for that period in column C:

Then in the next sheet I created an example with the Post in column A, post date in column B and submit by date in column C. The submit by column hosts the following formula:

=LOOKUP(2,1/('MRM Content Upload Calendar'!$A$2:$A$32<=B2)/('MRM Content Upload Calendar'!$B$2:$B$32>=B2),'MRM Content Upload Calendar'!$C$2:$C$32)

This checks which date range the date in column B falls within and returns the corresponding submit by date:

I would like to do something similar in my smartsheet but I read that the LOOKUP function doesn't work across sheets. If this true? And if so is there another way to set this up in Smartsheet to get the same result?

Thanks in advance

Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!