Index Match with Multiple criteria between time range (Excel)

Question

I am currently trying to solve this issue that I have been having. I am trying to get a value based on multiple criteria utilizing the INDEX MATCH formula. The following datasets are below for reference:

POST CLICKS TAB

Date    Time    Channel
10/2/2016   1:36:27 AM
10/13/2016  4:59:59 AM
10/15/2016  7:21:30 PM

PROSPECTS TAB

Time    Time -5 Time +5 Date    Channel
1:25:00 AM  1:20:00 AM  1:30:00 AM  10/2/2016   Website
1:28:00 AM  1:23:00 AM  1:33:00 AM  10/2/2016   Website
1:35:00 AM  1:30:00 AM  1:40:00 AM  10/2/2016   Website

So basically based on the Date(exact match) and Time(within range between Time-5 and Time+5 columns) values, I would like to fill in the "Channel" column in the "post click" dataset with the channel value from the "Prospects" dataset. As you can see the first row should be filled in with "website" while the last two should come up as N/A.

However, when using my formula below, all values come up as N/A.

=INDEX('Prospects (2)'!$F$2:$F$27054,MATCH(1,(F2='Prospects (2)'!$D$2:$D$27054)*AND('Post Click'!G2>='Prospects (2)'!$B$2:$B$27054,'Post Click'!G2<='Prospects (2)'!$C$2:$C$27054,1),0))

I have also included an AND function to include the 2 logics so that if the time value falls in between columns Time-5 and Time+5 it would come as true.

Could someone help with this issue? I tried researching through stackoverflow and have found similar issues but none with getting a value based off of multiple criteria and one of them being within a time range. Thank you!

P.S. -5 and +5 are in minutes.

Answer 1

Try this:

=INDEX($E$7:$E$9,SUMPRODUCT(MATCH(1,($B$7:$B$9<B2)*($C$7:$C$9>B2)*($D$7:$D$9=A2),0)))

Explanation

The main thing to dissect here is ($B$7:$B$9<B2)*($C$7:$C$9>B2)*($D$7:$D$9=A2) it creates three arrays of TRUE/FALSE which are multiplied together:

$B$7:$B$9<B2     *     $C$7:$C$9>B2    *   $D$7:$D$9=A2       =
     FALSE                FALSE                TRUE           0
     FALSE                FALSE                TRUE           0
     TRUE                 TRUE                 TRUE           1

Remembering that TRUE=1 and FALSE=0.

So now we have MATCH(1,{0,0,1},0) which makes a lot more sense. It will return 3 in this case. The only problem is that we used an array formula when we multiplied the arrays together so we either need to use Ctrl+Shift+Enter to enter the formula, or more conveniently, wrap the array-part in a SUMPRODUCT formula.

So SUMPRODUCT(MATCH(1,{0,0,1},0))=3 as expected and the INDEX function works as normal.