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

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:

Screenshot 2025-07-02 at 11.58.47 AM.png

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

Thanks

Find more posts tagged with

Accepted answers

All comments

Paul Newcome

Try this:

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.