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

Jemma Smith

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

Paul Newcome

It is possible. You are going to want to use something along the lines of...

=INDEX(COLLECT({Table Sheet Submit By Column}, {Table Sheet Period Start Column}, @cell <= [Post Date]@row, {Table Sheet Period End Column}, @cell >= [Post Date]@row), 1)

Paul Newcome

It is possible. You are going to want to use something along the lines of...

=INDEX(COLLECT({Table Sheet Submit By Column}, {Table Sheet Period Start Column}, @cell <= [Post Date]@row, {Table Sheet Period End Column}, @cell >= [Post Date]@row), 1)

Jemma Smith

Hi Paul - thanks so much for this! It worked a treat.

Paul Newcome

Great! Happy to help! 👍️