# 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:

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?

• ✭✭✭✭✭✭

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)

• ✭✭✭✭✭✭

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)

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

• ✭✭✭✭✭✭

Great! Happy to help! 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!