# LOOKUP function between two dates in Excel 2007

E

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

P

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

E

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

E

P

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

H

#### 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.