Lookup Formula Not working

I am attempting to do a lookup of a a Date (on the Deliverable Tracker, shown in screenshot #1). The lookup will use the Effective Start and End dates to find the appropriate range, then the end result will be the text value column at the far right. My formula shows a blank result, here is the formula:

=IFERROR(INDEX(COLLECT({Lookup Table_Text_Value}, {Lookup Table_StartDate}, <=[Date Submitted]@row , {Lookup Table_EndDate} >= [Date Submitted]@row , {Lookup Table_Primary}, "Option Period"), 1), "")

Find more posts tagged with

Formulas

collect

INDEX

error checking

lookup

Accepted answers

heyjay

Do you think you are missing a comma after the second lookup (END Date)?

=IFERROR(
INDEX(COLLECT({Lookup Table_Text_Value}, 
{Lookup Table_StartDate}, <=[Date Submitted]@row  , 
{Lookup Table_EndDate}, >= [Date Submitted]@row  , 
{Lookup Table_Primary}, "Option Period"), 1), "")

All comments

Jgorsich

Your formula looks okay, potentially you are getting something like an invalid column error. Your blank result could come from 2 sources - either you are collecting nothing or you are getting an error code and it is trapped by your iferror() statement. If you remove the iferror() statement, what is the final result?

heyjay

Do you think you are missing a comma after the second lookup (END Date)?

=IFERROR(
INDEX(COLLECT({Lookup Table_Text_Value}, 
{Lookup Table_StartDate}, <=[Date Submitted]@row  , 
{Lookup Table_EndDate}, >= [Date Submitted]@row  , 
{Lookup Table_Primary}, "Option Period"), 1), "")

Sherry Fox

@heyjay ,

PERFECT!!!!! That worked exactly as it should. Plus I like how you "formatted" the formula in a clean manner. This makes looking at the formula and following the logic very easy! Thanks so very much for all your help with this. I was going crazy trying to figure out why it would not work!

heyjay

You're welcome!

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.