Difference between last column in a table and another column

[Spiru]

Hi guys,

Could you please help with any ideas with the below problem that I have?

I would like to calculate in the column where it says "Variance" the difference between last column where it finds values and the column before it (April -March). I have added in cell G1 a dropdown list (bold) with the months and I was thinking to use this cell in an If function..but I don't know how

						April
January	February	March	April	May	June				Variance
10	32	54	76
13	43	76	44

<tbody>
</tbody>

Thank a lot in advance

 

[sheetspread]

Excel 2010
	A	B	C	D	E	F	G	H	I	J
1	anuary	February	March	April	May	June				Variance
2	10	32	54	76						22
3	13	43	76	44						-32
Sheet18

Cell Formulas
Range	Formula
J2	=INDEX($A2:$F2,MATCH(9.99999999999999E+26,$A2:$F2,1))-INDEX($A2:$F2,MATCH(9.99999999999999E+26,$A2:$F2,1)-1)

If you want to choose which month then change the match lookup term to the cell reference, the match range to the months row section, and the type to 0.

 

[Spiru]

Thanks a lot it works! But I couldn't manage to change the formula in order to work with the month.

 

[Spiru]

the formula seems to work in some cells in my excel file but for my table it doesn't. In the table I have formulas witch populate the cells with numbers. I have tried to change the format in my columns to numbers, general, accounting or even text but the formula still doesn't work. Any idea why? Thanks a lot for your help

 

[Spiru]

it looks like the formula doesn't work because of the formulas that I have in column E (May)...although vlookup brings 0. Maybe using the month from the dropdown list like I said in my first post would make it work?

 

[sheetspread]

This one goes by the month in I1:

Excel 2010
	A	B	C	D	E	F	G	H	I	J
1	January	February	March	April	May	June			April	Variance
2	10	32	54	76						22
3	13	43	76	44						-32
Sheet18

Cell Formulas
Range	Formula
J2	=HLOOKUP($I$1,$A$1:$F$3,ROW(A2),0)-INDEX($A2:$F2,MATCH($I$1,$A$1:$F$1,0)-1)

 

[Spiru]

thank you! It works