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?

As long as the dates don't overlap, try this in B2 of Sheet2:

=SUMPRODUCT((A2=Sheet1!A$2:A$10)*(A2<=Sheet1!B$2: B$10),Sheet1!C$2:C
$10)

Adjust the ranges to suit, then copy down.

Hope this helps.

Pete

On Sep 21, 3:55*pm, 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?

Excel 2007, Tables
With Sumifs, Chart.
All ranges dynamic.
http://www.mediafire.com/file/d2mmymmnk2n/09_21_09.xlsm

On Sep 21, 11:12*am, Pete_UK wrote:
As long as the dates don't overlap, try this in B2 of Sheet2:

=SUMPRODUCT((A2=Sheet1!A$2:A$10)*(A2<=Sheet1!B$2: B$10),Sheet1!C$2:C
$10)

Adjust the ranges to suit, then copy down.

Hope this helps.

Pete

On Sep 21, 3:55*pm, 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?- Hide quoted text -

- Show quoted text -

This works for the amount, thanks. Now how do I get it to retun
text? For example, I Sheet 1 had a fourth column listing the
insurance carrier's name and I wanted that to pull to sheet 2?

On Sep 21, 3:30*pm, Herbert Seidenberg
wrote:
Excel 2007, Tables
With Sumifs, Chart.
All ranges dynamic.http://www.mediafire.com/file/d2mmymmnk2n/09_21_09.xlsm

This is very cool. Do you know of anywhere I can get more info on the
functions used? For example where is Table2[Amt] defined? I can see
that Table2 range is defined, but do the brackets define the
individual amounts?

As long as the dates are in sequence (as they are in your example),
then you can put this in C2 of Sheet2:

=VLOOKUP(A2,Sheet1!A$2:D$10,4)

Again, adjust the ranges to suit your data in sheet 1 and then copy
down.

Hope this helps.

Pete

On Sep 22, 5:41*pm, excelCPA wrote:

This works for the amount, thanks. *Now how do I get it to retun
text? *For example, I Sheet 1 had a fourth column listing the
insurance carrier's name and I wanted that to pull to sheet 2

On Sep 22, 9:48*am, excelCPA wrote:
...do the brackets define the individual amounts?
Yes.
See Structured References in Excel 2007 Help.