VLOOKUP column array separator

Hi All,

I am try to use SUM() and VLOOKUP() to add numeric values of a particular table but the reference array to the columns doesnt seem to work. I have tried different separators ";", ",", "@", "\" and none seem to work. I dont get an error but it only takes the first element in the array. I have also tried to transpose the array but it didnt work either.

This is my formula:

=SUM(VLOOKUP(G19;$A$9:$G$17;{3;4;5;6};FALSE))

Any ideas? I have checked my settings and theoretically my column separator is "\", row separator ";" and alternate array item separator "@".

Thanks in advance!

Thats right!!!!

Thanks a lot!

Enter the formula with Ctrl+Shift+Enter

Now I am trying to do a similar thing with SUMPRODUCT() but I get a #VALUE error. This is what I am writing:

{=SUMPRODUCT($B$21:$G$21;VLOOKUP(G19;$A$9:$G$17;{2;3;4;5;6;7};FALSE))}

Any ideas?

Thanks!

The SUMPRODUCT equivalent is

=SUMPRODUCT(($B$21:$G$21)*($A$9:$A$17=G19)*(B9:G17))