Formula to Lookup Value Based on Dates

Options

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.

• Overachievers Alumni
Options

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:

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))

• Overachievers Alumni
Options

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:

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))

• Options

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

• Overachievers Alumni
Options

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

• edited 09/09/22
Options

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!