Index Match on an exact Number and a Date within a Range

[blakefraley]

Hey everyone,

I'm trying to perform data analysis and need to pull in a contract number based on an ID field and determine which record to pull for that ID based on where the Prod Date falls within the From/To Date Ranges (which are each a different column). My data has a unique record per ID and Date but I need to pull in a contract number for hundreds of thousands of records. I've tried using Index/Match with a match type of 1 but am not getting the expected results. The formula I tried was this:

{=INDEX('Detail'!$D$1:$D$1500,MATCH(A2&B2,Detail'!$A$1:$A$1500&Detail'!$B$1:$B$1500,1))}

Expected Results for Contract # (note that this is a subset of the 800,000 records I have)
'Results' Tab:
ID Prod Date Contract #
580678A 8/1/2016 1
580678A 1/1/2017 2
580678A 12/31/2016 1
580678A 4/1/2017 2
123A 8/1/2016 3
123B 1/1/2017 4

'Detail' Data tab:
ID From Date To Date Contract #
580678A 1/1/2016 12/31/2016 1
580678A 1/1/2017 12/31/2017 2
123A 1/1/2016 12/31/2016 3
123B 1/1/2017 12/31/2017 4

Any suggestions and help would be greatly appreciated.

 

[mart37]

=SUMPRODUCT((A2=Detail'!$A$1:$A$1500)*(B2>=Detail'!$B$1:$B$1500)*(B2<=Detail'!$C$1:$C$1500),'Detail'!$D$1:$D$1500)

 

[FDibbins]

Another option might be to use a helper column to ID those rows that meet your dates, and base the I/M on that?