LOOKUP function between two dates in Excel 2007

[excelCPA]

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 1olicy 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?

 

[Pete_UK]

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

 

[Herbert Seidenberg]

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

 

[excelCPA]

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









- 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?

 

[excelCPA]

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?

 

[Pete_UK]

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$10,4)

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

Hope this helps.

Pete

 

[Herbert Seidenberg]

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