Formula to Lookup Value Based on Dates

RogerDiaz

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.

MCorbin

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

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

RogerDiaz

@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

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

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