I have two spreadsheets with insurance claims data. The first sheet
lists effective policy start and end dates and applicable deductable
amounts. The second sheet is a listing of actual claims dates (I have
approximately 10,000 total).

I need to create a function column B of Sheet 2 in Excel similar to a
VLOOKUP that will pull the applicable Deducable_Amt to Sheet 2:Claims
Data (column B) from Sheet 1:Policy Data (column C) sheet based on if
the Claim_Date (column B) falls within the Start_Date and End_Date
(columns A & B) on Sheet 1.

For example, if the claim was filed on 10/01/2000 (column A sheet 2),
since the date falls between 09/01/2000 and 08/31/2001 (columns A & B
sheet 1), it will return $50,000 (column C, sheet 1) to the
Deductable_Amt (column B, sheet 2).



Sheet 1: Policy Data:
A B C

Start_Date End_Date Deductable_Amt
09/01/2000 08/31/2001 $50,000
09/01/2001 12/31/2002 $55,000
01/01/2003 11/30/2004 $60,000
12/01/2004 12/31/2006 $70,000

Sheet 2: Claims Data
A B
Claim_Date Deductable_Amt
10/01/2000 ???
12/15/2001 ???
07/01/2002 ???
09/17/2003 ???
11/30/2004 ???
02/21/2006 ???


Any ideas?

assuming you've entered true dates (and not text) the following should work
in cell B2:

=SUMPRODUCT(--('Sheet 1'!A$2:A$100<=A2),--('Sheet 1'!B2:B$100=A2),('Sheet
1'!C$2:C$100))

Copy down as needed.


Note that you can't callout entire columns (A:A) in SUMPRODUCT unless using
XL 2007
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"excelCPA" wrote:

I have two spreadsheets with insurance claims data. The first sheet
lists effective policy start and end dates and applicable deductable
amounts. The second sheet is a listing of actual claims dates (I have
approximately 10,000 total).

I need to create a function column B of Sheet 2 in Excel similar to a
VLOOKUP that will pull the applicable Deducable_Amt to Sheet 2:Claims
Data (column B) from Sheet 1:Policy Data (column C) sheet based on if
the Claim_Date (column B) falls within the Start_Date and End_Date
(columns A & B) on Sheet 1.

For example, if the claim was filed on 10/01/2000 (column A sheet 2),
since the date falls between 09/01/2000 and 08/31/2001 (columns A & B
sheet 1), it will return $50,000 (column C, sheet 1) to the
Deductable_Amt (column B, sheet 2).



Sheet 1: Policy Data:
A B C

Start_Date End_Date Deductable_Amt
09/01/2000 08/31/2001 $50,000
09/01/2001 12/31/2002 $55,000
01/01/2003 11/30/2004 $60,000
12/01/2004 12/31/2006 $70,000

Sheet 2: Claims Data
A B
Claim_Date Deductable_Amt
10/01/2000 ???
12/15/2001 ???
07/01/2002 ???
09/17/2003 ???
11/30/2004 ???
02/21/2006 ???


Any ideas?