Pulling Data from a Set Schedule

paulpenland

I am using Smartsheet for real estate management, and I am trying to figure out how to pull data from a set schedule. Specifically, if I have a lease that has a rent schedule that changes over time, I want to have a formula that pulls the rent being paid today. I can do this in Excel but can't seem to figure it out in Smartsheet. Here is my best example of what I'm trying to do:

RENT SCHEDULE

1/1/20 - 12/31/20: $1,000

1/1/21 - 12/31/21: $1,500

1/1/22 - 12/31/22: $2,000

Today's rent is: ________

Paul Newcome

Try something like this...

=INDEX({Rent Amount Column}, MATCH(MAX(COLLECT({Start Date Column}, {Start Date Column}, @cell <= TODAY())), {Start Date Column}, 0))