Add a date lookup to index Match formula

[JustHooch]

Hi,
I have the array formula below that is working correctly.

=IFERROR(INDEX('Roadmap'!$H:$H,MATCH(1,($A$3='Roadmap'!$D:$D)*(C$3='Roadmap'!$E:$E),0))&"","No Content Found")

What I need to do is add one additional lookup criteria that I can't quite figure out.

On the Roadmap tab there Column B = Start date and Column C = End Date.
The formula needs to look at the date in Cell B1 and find the row where the date is equal to or between Roadmaps column B & C.

I tried the below but it doesn't work. HELP!!!

=IFERROR(INDEX('Roadmap'!$H:$H,MATCH(1,($A$3='Roadmap'!$D:$D)*(C$3='Roadmap'!$E:$E)*(AND(B1>='Roadmap'!B:B,B1<='Roadmap'!C:C)),0))&"","No Content Found")

 

[Eric W]

Try:

=IFERROR(INDEX(Roadmap!$H:$H,MATCH(1,($A$3=Roadmap!$D:$D)*(C$3=Roadmap!$E:$E)*(B1>=Roadmap!B:B)*(B1<=Roadmap!C:C),0))&"","No Content Found")

with CSE. In this configuration, the * is the equivalent of AND.

Incidentally, for performance reasons, I'd recommend avoiding whole column references.

 

[JustHooch]

Thank you Eric W. That makes sense but it is not working. the result doesn't change regardless of the date I enter into cell B1.

 

[Eric W]

At this point, it's a bit tough to figure out what's going on without seeing a sample of your data. The first thing that comes to mind is to make sure that B1 is an actual Excel date, and that the B:C columns on Roadmap are also dates.

 

[JustHooch]

I can't figure out how to load my spreadsheet. maybe this will help.

but the formula seems to be only picking up "B1>='Roadmap'!B:B" and not "B1<='Roadmap'!C:C".


Example: on Roadmap tab
...........Column B....Column C....Column H
Row 1.. 8/10/18...... 8/12/18..... Picture
Row 2.. 8/13/18...... 8/15/18...... Image


Change the date in B1:
When I use the date 8/09/18, I get nothing - good
When I use the date 8/10/18, I get Picture - good
When I use the date 8/13/18, I get Picture - this should be Image

 

[JustHooch]

I was helped in another forum

The answer was

=IFERROR(INDEX(Roadmap[[Theme]:[JIRA Status]],MATCH(2,1/(($A$3=Roadmap[Module])*(B$3=Roadmap[Module Component])*($B$1>=Roadmap[Start Date])*($B$1<=Roadmap[Stop Date]))),MATCH($A4,Roadmap[[#Headers],[Theme]:[JIRA Status]],0))&"","No Content Found")

Array confirmed with Shift Ctrl Enter.

 

[Eric W]

Glad you got it. Thanks for the update.