Comparing Date Ranges in one Table to Date Ranges in another Table

[Mike C]

I am trying to compare a sheet with claims (with from and through
dates) to another sheet with amounts applicable to a different and
potentially overlapping set of date ranges.

In Sheet 1, each record consists of a claim ID, a member ID, and the
from and thru dates.

In Sheet 2, there is a Member ID, from and thru dates (more than one
set of dates per member), and an applicable dollar amount associated
with that date range.

What I am trying to do is to show the dollar amount in Sheet1 for each
claim that matches with the date ranges shown in Sheet 2.

Although my ultimate goal is to prorate (by day) the amounts in Sheet
2 to any instances where there is a match in Sheet 1, I am trying to
figure out how to first pull in the amounts from Sheet 2 in all
instances where there is a match with the dates in Sheet 1.

Is there a formula (perhaps using index and match functions), or a
series of steps I can take to produce that amount in sheet 1? Any
suggestions would be greatly appreciated.

So, to show the example.

Sheet 1 has:
Claim ID Member ID From Date Thru Date Amount
a33 555-333 4/1/05 5/30/05
FORMULA?
b22 312-444 3/7/07 6/15/08
FORMULA?
e44 312-444 7/7/08 8/15/08
FORMULA?
c12 767-575 etc etc

Sheet 2 has:
Member ID From Date Thru Date Amount
555-333 4/7/05 5/30/05 50
312-444 3/7/07 6/15/08 75
767-575 etc etc 200

 

[T. Valko]

Maybe this...

Sheet2!A$2:A$4 = ID
Sheet2!B$2:B$4 = From
Sheet2!C$2:C$4 = Thru
Sheet2!D$2$4 = values to sum

B2 = ID
C2 = From
D2 = Thru

=SUMPRODUCT(--(Sheet2!A$2:A$4=B2),--(C2<=Sheet2!B$2:B$4),--(D2>=Sheet2!C$2:C$4),Sheet2!D$2$4)

 

[Mike C]

Thanks Biff. The Sumproduct formula you gave was the template I
needed.

 

[T. Valko]

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP



Thanks Biff. The Sumproduct formula you gave was the template I
needed.