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.

[TABLE="width: 625"]
<colgroup><col><col><col span="3"><col><col><col><col span="3"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]201633[/TD]
[TD="align: right"]201634[/TD]
[TD="align: right"]201635[/TD]
[/TR]
[TR]
[TD="align: right"]34[/TD]
[TD] [/TD]
[TD]Subtotal[/TD]
[TD="align: right"]207[/TD]
[TD][/TD]
[TD]Brand[/TD]
[TD]Dept[/TD]
[TD]Dept Name[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD]Dept1[/TD]
[TD]Name1[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]78[/TD]
[TD="align: right"]26[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD]Dept2[/TD]
[TD]Name2[/TD]
[TD="align: right"]83[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]81[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD]Dept3[/TD]
[TD]Name3[/TD]
[TD="align: right"]84[/TD]
[TD="align: right"]86[/TD]
[TD="align: right"]67[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD]Dept4[/TD]
[TD]Name4[/TD]
[TD="align: right"]81[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD]Dept5[/TD]
[TD]Name5[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]68[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Subtotal: 0001[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]269[/TD]
[TD="align: right"]207[/TD]
[TD="align: right"]257[/TD]
[/TR]
</tbody>[/TABLE]

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))