Using the text from another cell to in a part of a array

[castropereira]

I have a table with some values that i want to sum according the year, name a store, and them according the current month...

(I want to sum the accumulated values since the beginning of the month)

I have a VLookup that gets me the text i want in the array:

[TABLE="width: 221"]
<colgroup><col width="221" style="width: 166pt;"></colgroup><tbody>[TR]
[TD="class: xl63, width: 221, bgcolor: #FCE4D6"]4[/TD]
[/TR]
[TR]
[TD="class: xl64"]4,5[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: #FCE4D6"]4,5,6[/TD]
[/TR]
[TR]
[TD="class: xl65"]4,5,6,7[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: #FCE4D6"]4,5,6,7,8[/TD]
[/TR]
[TR]
[TD="class: xl65"]4,5,6,7,8,9,10[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: #FCE4D6"]4,5,6,7,8,9,10,11[/TD]
[/TR]
[TR]
[TD="class: xl65"]4,5,6,7,8,9,10,11,12[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: #FCE4D6"]4,5,6,7,8,9,10,11,12,13[/TD]
[/TR]
[TR]
[TD="class: xl65"]4,5,6,7,8,9,10,11,12,13,14[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: #FCE4D6"]4,5,6,7,8,9,10,11,12,13,14,15[/TD]
[/TR]
[TR]
[TD="class: xl65"]4,5,6,7,8,9,10,11,12,13,14,15,16[/TD]
[/TR]
</tbody>[/TABLE]


And using a concatenate i get in the cell per. eg. F2 {[FONT=Arial, sans-serif]4,5,6,7,8,9,10,11,12}[/FONT]

[FONT=Arial, sans-serif]After getting this value i want to use it in my array:

[/FONT]=SUM(VLOOKUP(CONCATENATE(C2;C6);ORCAMENTOS!B:P;F2;FALSE))

By typing the value in F2 it all works by pressing ctr+shif+enter... with F2... no result...

i?m getting #ref as an error...

any helP

 

[steve the fish]

I stand to be corrected but i dont think you can do that with native formulas. I certainly dont know how if you can. If you numbers are always consecutive then there are other formulas you could use.

 

[Norie]

If the months are consecutive then you should be able to do this in another way, perhaps using SUMIF/SUMIFS.