Horizontal Sum (sum+match)

[FlashDota]

I want to make this formula: =sum('MTD Data'!S11:U11) dynamic.
=MATCH(A11,'MTD Data'!$B1:$B,0) returns the number 11,
so instead of S11:U11 I want to take something like sum('MTD Data'!S(MATCH):U(MATCH)) that automatically returns S11:U11

What way is there do it?

 

[etaf]

=SUM(INDIRECT(("S"&C2&":"&"U"&C2)))
will use a cell where you have the value 11
then you can just use the

so the match - returns the row number that =MATCH(A11,'MTD Data'!$B1:$B,0) finds

=SUM(INDIRECT(("S"&MATCH(A11,MTD Data'!$B1:$B100,0)&":"&"U"&MATCH(A11,MTD Data'!$B1:$B100,0))))

i have just kept on same sheet so you can see

Book7
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1				MATCH
2	6	1		11				=MATCH(A11,'MTD Data'!$B1:$B,0) returns the number 11,
3		2						so instead of S11:U11 I want to take something like sum('MTD Data'!S(MATCH):U(MATCH)) that automatically returns S11:U11
4		3
5		4
6
7
8
9
10
11	11	11																	1	2	3
12
13
Sheet1

Cell Formulas
Range	Formula
A2	A2	=SUM(INDIRECT(("S"&MATCH(A11,$B1:$B12,0)&":"&"U"&MATCH(A11,$B1:$B12,0))))
D2	D2	=MATCH(A11,$B1:$B12,0)

 

[RoryA]

You could use:

=SUM(INDEX('MTD Data'!$S:$U,MATCH(A11,'MTD Data'!$B:$B,0),0))

Are you using Google Sheets? $B1:$B is not a valid address in Excel.

 

[FlashDota]

You could use:

=SUM(INDEX('MTD Data'!$S:$U,MATCH(A11,'MTD Data'!$B:$B,0),0))

Are you using Google Sheets? $B1:$B is not a valid address in Excel.

Thanks @RoryA this was the solution. Looks like I just skipped one step (the index)
And yes I am using google sheets in fact.

 

[RoryA]

And yes I am using google sheets in fact.

I'd suggest you mention that in future. There are things that work in Excel that will not work the same, or at all, in Sheets.