Formula to Lookup Value Based on Dates
I'm trying to write a formula that will look up a value between two dates. Example noted below where the project lead is populated on the meeting request sheet based upon when the meeting is held.
I could make the project profile just have an "As of Date" if that makes things easier.
These projects can last a long time and many project lead changes may occur throughout the project.
Best Answer
-
Try this:
=IFERROR(INDEX(COLLECT({Lead}, {Start}, <=[Meeting Date]@row, {End}, >=[Meeting Date]@row), 1), INDEX(COLLECT({Lead}, {start}, <=[Meeting Date]@row, {End}, ISBLANK(@cell)), 1))
Where:
{Lead} is your Project Lead column in your Profile sheet
{Start} is your Start Date column in your Profile sheet
{End} is your end date column in your profile sheet.
Question - would the end date for Jake say "Current"? Or would it be blank?
If it's going to say "Current", you'd modify the formula like this:
=IFERROR(INDEX(COLLECT({Lead}, {start}, <=[Meeting Date]@row, {End}, >=[Meeting Date]@row), 1), INDEX(COLLECT({Lead}, {start}, <=[Meeting Date]@row, {End}, NOT(ISDATE(@cell))), 1))
Answers
-
Try this:
=IFERROR(INDEX(COLLECT({Lead}, {Start}, <=[Meeting Date]@row, {End}, >=[Meeting Date]@row), 1), INDEX(COLLECT({Lead}, {start}, <=[Meeting Date]@row, {End}, ISBLANK(@cell)), 1))
Where:
{Lead} is your Project Lead column in your Profile sheet
{Start} is your Start Date column in your Profile sheet
{End} is your end date column in your profile sheet.
Question - would the end date for Jake say "Current"? Or would it be blank?
If it's going to say "Current", you'd modify the formula like this:
=IFERROR(INDEX(COLLECT({Lead}, {start}, <=[Meeting Date]@row, {End}, >=[Meeting Date]@row), 1), INDEX(COLLECT({Lead}, {start}, <=[Meeting Date]@row, {End}, NOT(ISDATE(@cell))), 1))
-
@MCorbin Thank you for providing this.
I'm able to follow along until the "@cell", what should that be referencing? I'd have to adjust the formula each time a new row is added to the Project Profile sheet to reference the last cell on the End Date column?
I'd like to take this and "convert to column formula".
-
This formula will work as a column formula.
@cell essentially tells the formula to apply the calculation to the particular cell it's looking at. So in this case.... When looking at a cell in the "End Date" column, evaluate is it a Date or not?
Here's a help article that talks about it: https://help.smartsheet.com/articles/2476491-create-efficient-formulas-with-at-cell
-
Thank you for the clarification. I was able to get the formula working!!😁
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 497 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!