Loookup on multiple date range & multiple lines

[BrazenBigfoot]

Hello - I'm a beginner in Excel so my question may be very simple. I've gone through several threads for the past 45 minutes and cannot find one like this. I believe I may not need a Vlookup or an IF but possibly a INDEX and Match nested formula. I would be eternally grateful if you could assist me please.

I have data of a few volunteer's actual spend amounts and need to calculate the variance from the budget, so who over spent. Budgetary amounts are set based on the date of the year and location. Some locations can have from 1 - 6 date ranges with different amounts and some locations can have just 1.

This table right here shows an example of what the final result should be on the Budget column:

A	B	C	D	E	F
Volunteer	Location	Travel Start Date	Travel End Date	Actual Amount	Budget Amount
Suzie	Tokyo	1/9/2017	1/10/2017	120	131
Chip	Tokyo	4/11/2017	4/13/2017	240	131
Jenny	Tokyo	9/17/2017	9/22/2017	262	300

<tbody>
</tbody>

Sheet A - This is the reference tab with all the facts. Notice the date ranges for the locations.

A	B	C	D
Location	Start Date	End Date	Budget Amount
Tokyo	01/01/2017	05/15/2017	131
Tokyo	05/16/2017	09/15/2017	203
Tokyo	09/16/2017	12/31/2017	300
Australia	01/01/2017	05/14/2017	131
Australia	05/15/2017	12/31/2017	141
South Africa	01/01/2017	03/01/2017	210
South Africa	03/02/2017	12/31/2017	240

<tbody>
</tbody>

Sheet B- This show what the volunteers spent, their result. This is where I build my report. Each volunteer will have 1 line only.

A	B	C	D	E	F
Volunteer	Location	Travel Start Date	Travel End Date	Actual Amount	Budget Amount
Suzie	Tokyo	1/9/2017	1/10/2017	120	?
Chip	Tokyo	4/11/2017	4/13/2017	240	?
Jenny	Tokyo	9/17/2017	9/22/2017	262	?

<tbody>
</tbody>

 

[NiMip]

Re: Loookup on multiple date range & multiple lines - Mr. Excel Please Help!

Given you're trying to add up numbers, you can use an array based formula (i.e. use CONTROL+SHIFT+ENTER rather than just ENTER) as follows, starting in cell F2 on Sheet B and filling down:

=SUM(IF((B2='Sheet A'!$A$2:$A$8)*(C2>='Sheet A'!$B$2:$B$8)*(D2<='Sheet A'!$C$2:$C$8),'Sheet A'!$D$2:$D$8))