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

Advertisements

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

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

Hope this helps.

Pete
 
Ad

Advertisements

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top