Index Match to Grab Values on Another Sheet

[k3yn0t3]

Hi all--Can anyone help me update the formula in cell I43 (sheet "Fac_1") so that it matches the value in Fac_1 B43 with the match in Column A on the other sheet, "Assume_xl2bb" for its corresponding month & year column? There are 20 years of projections, shown monthly, in both sheets. I trimmed the cell range for simplicity below.

=+IF($D$5=1,FILTER(INDEX(Assume_xl2bb!$P$8:$MM$46,MATCH(Fac_1!$B43,Assume_xl2bb!$A$19:$A$1240,0),0),(Assume_xl2bb!$P$6:$MM$18=Fac_1!I$40)*(Assume_xl2bb!$F$18:$FG$18=Fac_1!I$39),0),0)

Thank you,
Sam

Factories_v24.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M
38								Q1	Q1	Q1	Q2	Q2
39								1	2	3	4	5
40								2028	2028	2028	2028	2028
41
42
43	2001	Transaction Volume % Growth						#VALUE!	#VALUE!
44	3001	Same-Store Sales % Growth
Fac_1

Cell Formulas
Range	Formula
I43:J43	I43	=+IF($D$5=1,FILTER(INDEX(Assume_xl2bb!$P$8:$MM$46,MATCH(Fac_1!$B43,Assume_xl2bb!$A$19:$A$1240,0),0),(Assume_xl2bb!$P$6:$MM$18=Fac_1!I$40)*(Assume_xl2bb!$F$18:$FG$18=Fac_1!I$39),0),0)

Factories_v24.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
16										Q1	Q1	Q1	Q2	Q2	Q2	Q3	Q3	Q3
17										1	2	3	4	5	6	7	8	9
18										2016	2016	2016	2016	2016	2016	2016	2016	2016
19	1000			Average Transaction Value % Growth
20	1001		1	Factory_1						2.4%	2.4%	2.4%	2.4%	2.4%	2.4%	2.4%	2.4%	2.0%
21						Live: Case 1				2.4%	2.4%	2.4%	2.4%	2.4%	2.4%	2.4%	2.4%	2.0%
22						Case 1				--%	--%	--%	--%	--%	--%	--%	--%	--%
23						Case 2				2.0%	2.0%	2.0%	2.0%	2.0%	2.0%	2.0%	2.0%	2.0%
24						Case 3
25						Case 4
26
27	1002		2	Factory_2
28						Live: Case 1				2.4%	2.4%	2.4%	2.4%	2.4%	2.4%	2.4%	2.4%	2.0%
29						Case 1				2.4%	2.4%	2.4%	2.4%	2.4%	2.4%	2.4%	2.4%	2.0%
30						Case 2				--%	--%	--%	--%	--%	--%	--%	--%	--%
31						Case 3				2.0%	2.0%	2.0%	2.0%	2.0%	2.0%	2.0%	2.0%	2.0%
32						Case 4				--%	--%	--%	--%	--%	--%	--%	--%	--%
33
38
39	2000			Average Price - % Growth YoY
40	2001		1	Factory 1						2.4%	2.4%	2.4%	2.4%	2.4%	2.4%	2.4%	2.4%	2.0%
41					Mgmt					2.4%	2.4%	2.4%	2.4%	2.4%	2.4%	2.4%	2.4%	2.0%
42					No growth					--%	--%	--%	--%	--%	--%	--%	--%	--%
43					Mid					2.0%	2.0%	2.0%	2.0%	2.0%	2.0%	2.0%	2.0%	2.0%
44
45	2002		2	Factory 2						1.3%	1.3%	1.3%	1.3%	1.3%	1.3%	1.3%	1.3%	2.0%
46					Mgmt					1.3%	1.3%	1.3%	1.3%	1.3%	1.3%	1.3%	1.3%	2.0%
47					No growth					--%	--%	--%	--%	--%	--%	--%	--%	--%
48					Mid					1.0%	1.0%	1.0%	1.0%	1.0%	1.0%	1.0%	1.0%	2.0%
49
Assume_xl2bb

Cell Formulas
Range	Formula
A20,A27	A20	=+A$19+C20
J20:R20,J45:R45,J40:R40,J28:R28	J20	=+OFFSET(J20,$F$5,)
F21,F28	F21	=+TEXT("Live: "&OFFSET(F21,$F$5,),)
C27	C27	=+C20+$F$15
A40,A45	A40	=+A$39+C40
C45	C45	=+C40+1

 

[k3yn0t3]

Figured this out!

 

[Peter_SSs]

Good to hear you got the solution.
If you would like to post the solution then it is perfectly fine to mark your post as the solution to help future readers. Otherwise, please do not mark a post that doesn't contain a solution.

 

[k3yn0t3]

Good to hear you got the solution.
If you would like to post the solution then it is perfectly fine to mark your post as the solution to help future readers. Otherwise, please do not mark a post that doesn't contain a solution.

Thanks, Peter. Noted.

Best, Sam