Excel VLOOKUP Date Range Split

Question

Thanks in advance for your time and consideration.

I am familiar with the concept of the VLOOKUP function in excel and was hoping for a little assistance. I have multiple tables in excel and have used the VLOOKUP command with success to do things such as fill cells with the full name, address, etc of an business when our internal business code is input. My current problem is this.

I have coverage tables indicating insurer X covers company Y over a given date range. I have data indicating company Y made Z dollars in earnings over another given date range. These dates most often do not match up nicely. I want to a sum of the total earnings associated with each insurer.

For example, Company Y was covered by Insurer A from 01/01/1980 to 04/15/1980 and Insurer B from 04/16/1980 until 06/20/1981. Company Y earned $250.00 from 01/01/1980 to 03/31/1980 therefore that $250.00 was covered by Insurer A, $0.00 by Insurer B as the date range doesn't include any time covered by Insurer B. Company Y earned $1000.00 from 04/01/1980 to 12/31/1980, No income the next year. 15 Days were covered by Insurer A, 260 days covered by Insurer B. Therefore $54.55 ((15/275)*1000) covered by Insurer A, $945.45 ((260/275)*1000) by Insurer B. Summed up $304.55 Insurer A, $945.45 Insurer B.

How would I go about doing this? If this is something beyond the features of excel, what would you recommend doing instead? Thank you so very much for your help, taking the time to answer questions here is appreciated.

It should be noted that my coverage tables are complete already. The data I have to input is the business, earnings period start and end dates, and total earnings in that period. The coverage tables include business, insurer, coverage period start and end dates. They are on different workbooks, but I am familiar how to reference external data on other workbooks.

Here is an example of the how the data is structured. https://docs.google.com/spreadsheets/d/1nSotP9TYgyKeL0y_CeQXEwfs-ygaPW17LSMf9NOh6GY/edit?usp=sharing

Answer 1

The following is from your example. You can do it with the help of sumproduct.

Left side is your insurer table. Right side is the claim table. The below is the formula you can use. As mentioned earlier. it is bit complex and works well.

=IFERROR(SUMPRODUCT(1*($G$2:$G$5>=C2)*($H$2:$H$5<=D2)*$I$2:$I$5),0)+IFERROR(SUMPRODUCT(1*($G$2:$G$5<C2)*($H$2:$H$5>D2)*(D2-C2+1)/($H$2:$H$5-$G$2:$G$5+1)*($I$2:$I$5)),0)+IFERROR(SUMPRODUCT(1*($G$2:$G$5>=C2)*($G$2:$G$5<=D2)*($H$2:$H$5>D2)*(D2-$G$2:$G$5+1)/($H$2:$H$5-$G$2:$G$5+1)*($I$2:$I$5)),0)+IFERROR(SUMPRODUCT(1*($H$2:$H$5<=D2)*($H$2:$H$5>=C2)*($G$2:$G$5<C2)*($H$2:$H$5-C2+1)/($H$2:$H$5-$G$2:$G$5+1)*($I$2:$I$5)),0)

For Sample Excel Click Here

Answer 2

Assuming that we have two tables: 1-INSURERS TABLE:

Insurers |   Begins |   Ends   |
    A    |01/01/1980|04/15/1980|
    B    |04/16/1980|06/20/1981|

And 2-EARNGINGS TABLE:

 #|Amount |  Begins  |   Ends   |
 1|   250 |01/01/1980|03/31/1980|
 2|  1000 |04/01/1980|12/31/1980|

Then we will add some extra auxiliary columns at the end of 2-table:

 #|Amount |  Begins  |   Ends   |       Days       |      A       |      B       |
 1|   250 |01/01/1980|03/31/1980|=[ends]-[begins]+1|[1-days for A]|[1-days for B]|
 2|  1000 |04/01/1980|12/31/1980|=[ends]-[begins]+1|[2-days for A]|[2-days for B]|

Put this Formula into [1-days for A] to calculate days for companies and fill down from [1-days for A] to [2-days for B] using mouse :

=IF(MIN(VLOOKUP(L$3,$B$4:$D$6,3,FALSE),$J4)-MAX(VLOOKUP(L$3,$B$4:$D$6,2,FALSE),$I4)+1>0,MIN(VLOOKUP(L$3,$B$4:$D$6,3,FALSE),$J4)-MAX(VLOOKUP(L$3,$B$4:$D$6,2,FALSE),$I4)+1,0)

To get result, we should create 3-table "RESULT TABLE":

#|           A          |            B           |
1|[1-amount belongs to A]|[1-amount belongs to B]|
2|[2-amount belongs to A]|[2-amount belongs to B]|

Put this formula into [1-amount belongs to A] to calculate companies' portion and fill using mouse:

=HLOOKUP(C$16,$G$3:$N$6,MATCH($B17,$G$3:$G$6,0),FALSE)/VLOOKUP($B17,$G$3:$N$6,5,FALSE)*VLOOKUP($B17,$G$3:$N$6,2,FALSE)

It's a bit complicated, to understand, use my example file as well. I hope I understand your problem.