Using LEFT and RIGHT with an INDEX MATCH MATCH?

[Lewzerrrr]

Hi,

I think quite tricky situation and I may be going about it the wrong way.

I have 3 sheets.

Sheet1 has a value in C3 which is my week number, 34 = Week 34, 35 = Week 35, I manually change this every week.
Sheet2 is my summary page, I need to input my formula in this sheet. In this sheet I have the word "Subtotal" in A3 (This is what I'm going to match.
Sheet3 has my data input.

In sheet 3 what remains constant is row 1 which has my YEARWEEK numbers e.g 201601, 201602.. so I would need a look up to Sheet1 C3 matching to A1:AA1 RIGHT(Cell,2). Also in column A the word "Subtotal" remains constant but the end of the string changes so I would need a LEFT(Cell,8).

The INDEX

So the whole INDEX MATCH MATCH is dynamic that when I change the week number in Sheet1, the value in Sheet2 G3 will update with the Subtotal in Sheet3.

Is this possible?

Small sample data without different worksheets, I can add these in when inputting the formula.

								201633	201634	201635
34		Subtotal	207		Brand	Dept	Dept Name
						Dept1	Name1	19	78	26
						Dept2	Name2	83	6	81
						Dept3	Name3	84	86	67
						Dept4	Name4	81	19	15
						Dept5	Name5	2	18	68
					Subtotal: 0001			269	207	257

<colgroup><col><col><col span="3"><col><col><col><col span="3"></colgroup><tbody>
</tbody>

So if I was to change the 34 to 35, the subtotal would change to 257.
But also the Depts get bigger each data input.

Thanks,

 

[Lewzerrrr]

Okay, managed to do it with helper columns, thanks.

If there's a way without helper that would be amazing to learn!

 

[AlanY]

try this

Excel 2012
	A	B	C	D	E	F	G	H	I
1							201633	201634	201635
2	35	Subtotal	257	Brand	Dept	Dept Name
3					Dept1	Name1	19	78	26
4					Dept2	Name2	83	6	81
5					Dept3	Name3	84	86	67
6					Dept4	Name4	81	19	15
7					Dept5	Name5	2	18	68
8				Subtotal: 0001			269	207	257
Sheet2

Cell Formulas
Range	Formula
C2	{=INDEX(G:I,MATCH(B2&"*",D:D,0),MATCH("*"&A2,G1:I1&"",0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[AliGW]

This maybe?

Excel 2016 (Windows) 32 bit
	A	B	C	D	E	F	G	H	I	J	K
1									201633	201634	201635
2	34		Subtotal	207		Brand	Dept	Dept Name
3							Dept1	Name1	19	78	26
4							Dept2	Name2	83	6	81
5							Dept3	Name3	84	86	67
6							Dept4	Name4	81	19	15
7							Dept5	Name5	2	18	68
8						Subtotal: 0001			269	207	257
Sheet2

Cell Formulas
Range	Formula
D2	=INDEX($I$1:$K$5000,MATCH(C2&"*",$F$1:$F$5000,0),MATCH(VALUE(2016&A2),$I$1:$K$1,0))