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:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]Volunteer[/TD]
[TD]Location[/TD]
[TD]Travel Start Date[/TD]
[TD]Travel End Date[/TD]
[TD]Actual Amount[/TD]
[TD]Budget Amount[/TD]
[/TR]
[TR]
[TD]Suzie[/TD]
[TD]Tokyo[/TD]
[TD]1/9/2017[/TD]
[TD]1/10/2017[/TD]
[TD]120[/TD]
[TD]131[/TD]
[/TR]
[TR]
[TD]Chip[/TD]
[TD]Tokyo[/TD]
[TD]4/11/2017[/TD]
[TD]4/13/2017[/TD]
[TD]240[/TD]
[TD]131[/TD]
[/TR]
[TR]
[TD]Jenny[/TD]
[TD]Tokyo[/TD]
[TD]9/17/2017[/TD]
[TD]9/22/2017[/TD]
[TD]262[/TD]
[TD]300[/TD]
[/TR]
</tbody>[/TABLE]

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

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]Location[/TD]
[TD]Start Date[/TD]
[TD]End Date[/TD]
[TD]Budget Amount[/TD]
[/TR]
[TR]
[TD]Tokyo[/TD]
[TD]01/01/2017[/TD]
[TD]05/15/2017[/TD]
[TD]131[/TD]
[/TR]
[TR]
[TD]Tokyo[/TD]
[TD]05/16/2017[/TD]
[TD]09/15/2017[/TD]
[TD]203[/TD]
[/TR]
[TR]
[TD]Tokyo[/TD]
[TD]09/16/2017[/TD]
[TD]12/31/2017[/TD]
[TD]300[/TD]
[/TR]
[TR]
[TD]Australia[/TD]
[TD]01/01/2017[/TD]
[TD]05/14/2017[/TD]
[TD]131[/TD]
[/TR]
[TR]
[TD]Australia[/TD]
[TD]05/15/2017[/TD]
[TD]12/31/2017[/TD]
[TD]141[/TD]
[/TR]
[TR]
[TD]South Africa[/TD]
[TD]01/01/2017[/TD]
[TD]03/01/2017[/TD]
[TD]210[/TD]
[/TR]
[TR]
[TD]South Africa[/TD]
[TD]03/02/2017[/TD]
[TD]12/31/2017[/TD]
[TD]240[/TD]
[/TR]
</tbody>[/TABLE]

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

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]Volunteer[/TD]
[TD]Location[/TD]
[TD]Travel Start Date[/TD]
[TD]Travel End Date[/TD]
[TD]Actual Amount[/TD]
[TD]Budget Amount[/TD]
[/TR]
[TR]
[TD]Suzie[/TD]
[TD]Tokyo[/TD]
[TD]1/9/2017[/TD]
[TD]1/10/2017[/TD]
[TD]120[/TD]
[TD] ?[/TD]
[/TR]
[TR]
[TD]Chip[/TD]
[TD]Tokyo[/TD]
[TD]4/11/2017[/TD]
[TD]4/13/2017[/TD]
[TD]240[/TD]
[TD] ?[/TD]
[/TR]
[TR]
[TD]Jenny[/TD]
[TD]Tokyo[/TD]
[TD]9/17/2017[/TD]
[TD]9/22/2017[/TD]
[TD]262[/TD]
[TD] ?[/TD]
[/TR]
</tbody>[/TABLE]

 

[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))