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
-
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)
Answers
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!