Index Match QTD?

[amc1528]

Hello. I am trying to see if it's possible to create a formula that can do a QTD (quarter to date) or YTD (dynamic range) formula with index, match, match. I have index match match which gives me a single month's data, and I have a QTD index match formula, which can give me the qtd data through a specific month, but not a specific account. Is it possible to have both? I'm hoping that it's a simple thing for someone out there. Thanks!

 

[VBABEGINER]

Apologies could not help you. But I remember strongly, the same post I have seen on MrExcel. May b you will get..by doing any search..Cheers..

 

[amc1528]

thanks for trying. I searched mrexcel first, prior to posting, and was unable to find a solution.
Now I am hoping someone out there is able to assist.
Thanks

 

[jorismoerings]

Hi,

Not using index;match but sum/offset.

try this:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1		Jan	feb	Mrt	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	FY
2	Net Sales	1000	1000	2500	1000	1000	2500	1000	1000	2500	1000	1000	2500	18000
3	COGS	250	250	300	250	250	300	250	250	300	250	250	300	3200
4	Gross IMU	750	750	2200	750	750	2200	750	750	2200	750	750	2200	14800
5	IGM	795	795	2245	795	795	2245	795	795	2245	795	795	2245	15340
6
7				Apr	May
8		QTD	Single Month	QtD If April	QTD If May
9	in millions	w/o account lookup	Index Match	Should be	Should be
10	Net Sales		1000	1000	2000
11	COGS		250	250	500
Sheet1

Cell Formulas
Range	Formula
N2	=SUM(B2:M2)
N3	=SUM(B3:M3)
N4	=SUM(B4:M4)
N5	=SUM(B5:M5)
C10	=OFFSET($A$1,MATCH($A10,$A$2:$A$5,0),MATCH($D$7,$B$1:$M$1,0))
C11	=OFFSET($A$1,MATCH($A11,$A$2:$A$5,0),MATCH($D$7,$B$1:$M$1,0))
D10	=OFFSET($A$1,MATCH($A10,$A$2:$A$5,0),MATCH($D$7,$B$1:$M$1,0),1,(INT(MATCH($D$7,$B$1:$M$1,0)/3)*3)-MATCH($D$7,$B$1:$M$1,0))
D11	=OFFSET($A$1,MATCH($A11,$A$2:$A$5,0),MATCH($D$7,$B$1:$M$1,0),1,(INT(MATCH($D$7,$B$1:$M$1,0)/3)*3)-MATCH($D$7,$B$1:$M$1,0))
E10	=SUM(OFFSET($A$1,MATCH($A10,$A$2:$A$5,0),(INT(MATCH($E$7,$B$1:$M$1,0)/3)*3)+1,1,MATCH($E$7,$B$1:$M$1,0)-(INT(MATCH($E$7,$B$1:$M$1,0)/3)*3)))
E11	=SUM(OFFSET($A$1,MATCH($A11,$A$2:$A$5,0),(INT(MATCH($E$7,$B$1:$M$1,0)/3)*3)+1,1,MATCH($E$7,$B$1:$M$1,0)-(INT(MATCH($E$7,$B$1:$M$1,0)/3)*3)))