Looking Up Value Based on Date Range that a Date Falls In

refry

Hi

I'm looking to create a lookup that uses a date field (pub date) and then pulls a value called Selling Season based on the date range that pub date falls within.

Current formula that isn't working:

=INDEX(COLLECT({Selling Season}, {Pub Date Start}, [Pub Date]@row >= {Pub Date Start}, {Pub Date End}, [Pub Date]@row <= {Pub Date End}), 1)

Table where the Selling Season values and date ranges are stored:

Appreciate any help on the above, as current formula is returning #INVALID VALUE

Thanks

Paul Newcome

Try this:

=INDEX({Selling Season}, MATCH(MAX(COLLECT({Pub Date Start}, {Pub Date Start}, @cell <= [Pub Date]@row)), {Pub Date Start}, 0))