Need formula to calculate YTD variance of current and prior year

I'm building a spreadsheet where I have 3 variances that I am concerned with:

1. Current Month Better/(Worse) Prior Month - I use an offset formula to look at the two specific columns
2. Current Month Better/(Worse) Prior Year - I use the same formula as above but point to the same month in the previous year
3. Current Month Year-to-Date Better/(Worse) Prior Year-to-Date

It is this third formula that I am having trouble with. Currently I copy the current month column and insert to the left. That column does a vLookup on another sheet to get the correct values. I need the YTD variance column to continually grow each month so that it captures Jan to May 2014 less Jan to May 2013. Then the next month as I insert the new month's column I need that formula to "automatically" update to calculate Jan to Jun 2014 less Jan to Jun 2013.

Cell A1 has the first day of the current month so I was thinking of using the "Month" formula to convert that to a growing variable which equates to the number of columns to use. Then use that in conjunction with the offset formula. But I'm not sure how to do this. I've attached a sample spreadsheet to assist you. Thanks in advance!

Try these...

C4=SUMPRODUCT(($G4:$W4)*(YEAR($G$3:$W$3)=YEAR($A$1)-1)*(MONTH($G$3:$W$3)<=MONTH($A$1)))-SUMPRODUCT(($G4:$W4)*(YEAR($G$3:$W$3)=YEAR($A$1)))
(Might have that 1 swapped around?)
D4=INDEX($G4:$W4,MATCH(EDATE($A$1,-12),$G$3:$W$3,0))-INDEX($G4:$W4,MATCH($A$1,$G$3:$W$3,0))
E4=INDEX($G4:$W4,MATCH(EDATE($A$1,-1),$G$3:$W$3,0))-INDEX($G4:$W4,MATCH($A$1,$G$3:$W$3,0))

With these, you wont need to fiddle with the offset values each month, they will update automatically when you change the date in A1

Basic formula is this...
INDEX($G4:$W4,MATCH($A$1,$G$3:$W$3,0))

INDEX() gives the range - in this case that row of data
MATCH() finds which column to look in
find the column that the date in A1 is in
the EDATE() part calcs the date based on a given date (A1) x-months from then, so I used -12 to go back 1 year

=SUMPRODUCT(($G4:$W4)*(YEAR($G$3:$W$3)=YEAR($A$1)-1)*(MONTH($G$3:$W$3)<=MONTH($A$1)))

this is adding all values in the row ($G4:$W4) that meet your criteria...
(YEAR($G$3:$W$3)=YEAR($A$1)-1) the year = 2014-1=2013
(MONTH($G$3:$W$3)<=MONTH($A$1) the mont this <= the month in A1

Glad it worked for you