SUMMING AREA with index/match or hlook up/sum REAL PROBLEM - HELP

[Dmrs92]

Hello,
I have a problem with function:
I would like to sum area form row 11 till 54 but with hlookup. But the problem is that the number of rows will grow so I don;t want to write every row in brackets. i thought of index but the second problem is that the summing are is always one column before found value.
So Index plus offset plus match. I don't know how to do it.

So look in row 9 for value in other sheet in for example a2 when value is found in cell b3, sum area from A4:A52.
INDEX(A4:54A;MATCH(A2;9:9;0);column i don't know -1 but it don;t work, ........... no idea. Formula always look in same sheet but looking value changes (btw. ther are weeks week 1, week 2 , week 3 ect.)


Thank you in advance,

 

[DanteAmor]

You could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.




Explain the example of the sheets of what you have and what you expect as a result.