Add a date lookup to index Match formula

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")

Still end up with the same issue though I did convert the roadmap tab to a table so that should fix the time issue.

=IFERROR(INDEX(Roadmap[Media],MATCH(1,($A$3=Roadmap[Module])*(C$3=Roadmap[Module Content])*((B1>=Roadmap[Start Date])*(B1<=Roadmap[Stop Date])),0))&"","No Content Found")

Attached is a sample of the spreadsheet. The formulas we are discussing is on the Marketing Tab in cell B4:C8, then repeats for each section.

That shows us the problem but not what is expected.

To make sure I have the logic right before trying this.

Compare B2 to start date and stop date
Compare A3 (then A10, A17, etc) to the Module column.
Compare B3:C3 (B10:E10, B17:G17, etc) to Module Component.

Find the row where all ove the above match, then

Compare A4:A8, (A11:A15, A18:A22, etc) to the Roadmap column headings in F1:O1

and get the result from that column.

Have I got that right?

That is correct.

(Date in B1 not B2)

In that case

=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.

However, you should note that you may not get the expected results in many cases because your headers do not match, in Roadmap you have 'First Call To Action (CTA) Copy' but in Marketing you have 'First Call to Action'

The formula will not see these as the same.

Jason.b75- I ran into a snag.

It was requested that I also pull the dates into the Marketing tab. I updated the formula to the below but my result for the date fields are unformatted. The cell format is "Date" however the result is still showing as 43325. I am guessing that becasue this is an array formula the result is being read by Excel as text but I'm not sure how to modify the formula to correct for that.

=IFERROR(INDEX(Roadmap[[Start Date]:[JIRA Status]],MATCH(2,1/(($C$3=Roadmap[Module])*('Dropdown Values'!$B$2=Roadmap[Module Component])*($C$2>=Roadmap[Start Date])*($C$2<=Roadmap[Stop Date]))),MATCH($B9,Roadmap[[#Headers],[Start Date]:[JIRA Status]],0))&"","No Content Found")

Just remove &"" from near the end of the formula, that is what is converting any numbers in the results to text.

Again thank you. YOU ROCK!!