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

  • MCorbin
    MCorbin Overachievers Alumni
    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

  • MCorbin
    MCorbin Overachievers Alumni
    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))

  • @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".

  • MCorbin
    MCorbin Overachievers Alumni

    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

  • RogerDiaz
    RogerDiaz ✭✭
    edited 09/09/22

    Thank you for the clarification. I was able to get the formula working!!😁

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!