Dynamic sum imbedded in a vlookup

[casco]

Hi all I'm in need of some advise in regards to a dynamic sum imbedded in a VLOOKUP.

I'm working on a spread sheet that will need to pick up values, using a VLOOKUP, in a second tab, which is easy enough, however my problem arises when I get to the requirements of summing the year to date. So the first VLOOKUP for a given month is working, but when I get to the second month or more of the financial year I will need to sum up all the previous month. ie Jan + Feb when reporting on Feb and Jan, Feb and Mar when reporting on March and so on.

A sample table is below

	A	B	C	D	E	F	G	H	I	J
1		Jan	Feb	Mar	Apr	May	Jun	Jul	Aug
2	Sales	100000	300000	200000	50000	75000	200000	50000	20000
3

Formula should pick up the sales values of a given month based on a drop down, for example Mar, but it should then sum the month from March and back to Jan - in this example it should return 600000.

to pick up the correct month I'm using the following formula =VLOOKUP (criteria, range in tap, Match(month drop down, range ,0),0). Using the table above it would be =VLOOKUP ( sales , A:I , Match ( Mar , A:I , 0), 0)

The problem comes when I need to sum the month from march and back to Jan, in the same VLOOKUP formula. obviously this will also have to work when the Match is looking for Aug.

Any input will be appreciated.

I'm using Office 2010

Best
Martin

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1		Jan	Feb	Mar	Apr	May	Jun	Jul	Aug
2	Sales	100000	300000	200000	50000	75000	200000	50000	20000				Mar	600000
3
Master

Cell Formulas
Range	Formula
N2	N2	=SUM(B2:INDEX(B2:I2,MATCH(M2,B1:I1,0)))

 

[casco]

=SUM(B2:INDEX(B2:I2,MATCH(M2,B1:I1,0)))

HI Thanks for the reply. The formula works on it's own and with the dropdown, but when include in the VLOOKUP I described the whole formula returns a #REF! error unfortunately. Is this because the Col_Index_num in the VLOOKUP formula cant handle the sum formula?

I have gone through the VLOOKUP formula in detail and cant see any errors in it.

Any suggestions?

Best

Martin

 

[Fluff]

Why are you including it in a vlookup? Just use the formula as-is