Index between a group of dates to return a corresponding row value

Lauren Y

I need to add a formula with the "period end date" to have it automatically update to return the "period rate year" that it falls within - e.g., 1/31/25 falls within 4/1/24 and 6/30/25 so it returns 2024 in the yellow.

DKazatsky2

Hi @Lauren Y,

Give this a try.

=INDEX(COLLECT([Rate Year]$2:[Rate Year]$8, [Rate Year Start]$2:[Rate Year Start]$8, <=[Period End Date]@row , [Rate Year End]$2:[Rate Year End]$8, >=[Period End Date]@row ), 1)

Hope this helps,

Dave

Lauren Y

this works - but is there a more efficient way?

DKazatsky2

Hi @Lauren Y,

Give this a try.

=INDEX(COLLECT([Rate Year]$2:[Rate Year]$8, [Rate Year Start]$2:[Rate Year Start]$8, <=[Period End Date]@row , [Rate Year End]$2:[Rate Year End]$8, >=[Period End Date]@row ), 1)

Hope this helps,

Dave