Cumulative value based on row and column lookup

[hianc]

[TABLE="width: 1078"]
<tbody>[TR]
[TD] The sheet "Summary P&L" is my report format. I used the formula below to find the Mth Actual result. The cell $G$3 contains the month of the report & $N7 contains value "Grants". I know I can use a work around by creating another sheet containing the cumulative result from the monthly result and then use the same lookup formula, but I want simplify this as I am developing this report for someone else. Any help would be appreciated as I am doing this as a finance volunteer for a NFP.

=VLOOKUP('Summary P&L'!$N7,'P&L FY16'!$D$1:$AC$120,VLOOKUP($G$3,Validations!$C$8:$D$19,2,FALSE),FALSE)


[TABLE="width: 484"]
<tbody>[TR]
[TD]
Sheet named "Summary P&L"[/TD]
[TD][/TD]
[TD]Income/Expense Statement[/TD]
[TD][/TD]
[TD]Month:-[/TD]
[TD]Dec-15[/TD]
[/TR]
[TR]
[TD="colspan: 2"]Mth[/TD]
[TD][/TD]
[TD="colspan: 3"]Year to Date[/TD]
[/TR]
[TR]
[TD]Actual[/TD]
[TD]Budget[/TD]
[TD][/TD]
[TD]Actual[/TD]
[TD]Budget[/TD]
[TD]LYear[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]INCOME[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD="align: right"]11,667[/TD]
[TD]Grants[/TD]
[TD="align: right"]70,000[/TD]
[TD="align: right"]80,000[/TD]
[TD="align: right"]14,878[/TD]
[/TR]
[TR]
[TD="align: right"]60[/TD]
[TD="align: right"]2,500[/TD]
[TD]Fundraising - Gifts[/TD]
[TD="align: right"]6,300[/TD]
[TD="align: right"]12,200[/TD]
[TD="align: right"]6,012[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Sheet named "'P&L FY16"
[TABLE="width: 711"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Jul 15[/TD]
[TD][/TD]
[TD]Aug 15[/TD]
[TD][/TD]
[TD]Sep 15[/TD]
[TD][/TD]
[TD]Oct 15[/TD]
[TD][/TD]
[TD]Nov 15[/TD]
[TD][/TD]
[TD]Dec 15[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 3"]4100000 · GRANTS[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]4110000 · Grants Other[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]4110003 · Grants - Other[/TD]
[TD="align: right"]20,000.00[/TD]
[TD][/TD]
[TD="align: right"]0.00[/TD]
[TD][/TD]
[TD="align: right"]0.00[/TD]
[TD][/TD]
[TD="align: right"]50,000.00[/TD]
[TD][/TD]
[TD="align: right"]0.00[/TD]
[TD][/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]Total 4110000 · Grants Other[/TD]
[TD="align: right"]20,000.00[/TD]
[TD][/TD]
[TD="align: right"]0.00[/TD]
[TD][/TD]
[TD="align: right"]0.00[/TD]
[TD][/TD]
[TD="align: right"]50,000.00[/TD]
[TD][/TD]
[TD="align: right"]0.00[/TD]
[TD][/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD="colspan: 3"]Grants[/TD]
[TD="align: right"]20,000.00[/TD]
[TD][/TD]
[TD="align: right"]0.00[/TD]
[TD][/TD]
[TD="align: right"]0.00[/TD]
[TD][/TD]
[TD="align: right"]50,000.00[/TD]
[TD][/TD]
[TD="align: right"]0.00[/TD]
[TD][/TD]
[TD="align: right"]0.00[/TD]
[/TR]
</tbody>[/TABLE]

Sheet named "Validations"

[TABLE="width: 128"]
<tbody>[TR]
[TD="class: xl65, width: 64, align: right"]Jul-15[/TD]
[TD="width: 64, align: right"]4[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]Aug-15[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]Sep-15[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]Oct-15[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]Nov-15[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]Dec-15[/TD]
[TD="align: right"]14[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]Jan-16[/TD]
[TD="align: right"]16[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]Feb-16[/TD]
[TD="align: right"]18[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]Mar-16[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]Apr-16[/TD]
[TD="align: right"]22[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]May-16[/TD]
[TD="align: right"]24[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]Jun-16[/TD]
[TD="align: right"]26[/TD]
[/TR]
</tbody>[/TABLE]

 

[Comfy]

Here you go:

Excel 2010
	A	B	C	D	E	F
1				Dec 15
2
3	Actual	Budget		Actual	Budget	LYear
4	0	11,667	Grants	75,000	80,000	14,878
5	0	11,667	Grants	75,000	80,000	14,878
Summary P&L

Cell Formulas
Range	Formula
D4	=SUMPRODUCT('P&L FY16'!$D$4:$N$8*('P&L FY16'!$A$4:$A$8='Summary P&L'!C4)*(COLUMN('P&L FY16'!$D$3:$N$3)<=VLOOKUP($D$1,Validations!$A$1:$B$12,2,FALSE)))
D5	=SUMPRODUCT('P&L FY16'!$D$4:$N$8*('P&L FY16'!$A$4:$A$8='Summary P&L'!C5)*('P&L FY16'!$D$3:$N$3<=$D$1))

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
3				Jul 15		Aug 15		Sep 15		Oct 15		Nov 15		Dec 15
4	Grants			20000		0		0		50000		0		5000
P&L FY16

Excel 2010
	A	B
1	Jul-15	4
2	Aug-15	6
3	Sep-15	8
4	Oct-15	10
5	Nov-15	12
6	Dec-15	14
7	Jan-16	16
8	Feb-16	18
9	Mar-16	20
10	Apr-16	22
11	May-16	24
12	Jun-16	26
Validations

The first formula uses your validation sheet (I have assumed that the numbers in the second column reflect the column in which the month resides).

The second formula just looks at the dates (this assumes that your heading are dates rather than text representations of dates)