Cumulative value based on row and column lookup

[hianc]

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) Sheet named "Summary P&L" Income/Expense Statement Month:- Dec-15 Mth Year to Date Actual Budget Actual Budget LYear INCOME 0 11,667 Grants 70,000 80,000 14,878 60 2,500 Fundraising - Gifts 6,300 12,200 6,012 <tbody> </tbody>

<tbody>
</tbody>

Sheet named "'P&L FY16"

			Jul 15		Aug 15		Sep 15		Oct 15		Nov 15		Dec 15
4100000 · GRANTS
	4110000 · Grants Other
		4110003 · Grants - Other	20,000.00		0.00		0.00		50,000.00		0.00		0.00
	Total 4110000 · Grants Other	20,000.00		0.00		0.00		50,000.00		0.00		0.00
Grants	20,000.00		0.00		0.00		50,000.00		0.00		0.00

<tbody>
</tbody>

Sheet named "Validations"

Jul-15	4
Aug-15	6
Sep-15	8
Oct-15	10
Nov-15	12
Dec-15	14
Jan-16	16
Feb-16	18
Mar-16	20
Apr-16	22
May-16	24
Jun-16	26

<tbody>
</tbody>

 

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