Don,
I think you misread what I said. I said if you change c2 from 9 to 1 the
answer should be 9.
The data in A2 to F2 is variable so if for example each of the six cells (a2
to f2) had the figure "1" in them, we apply a value of "3" to each from the
table J2:K4) and sum 6 times 3 = 18.
If you put "1" into each of cells a2:f2 I think you will see the result
comes up as 3 when it should be 18. I can not see why this is so.
However I am happy as Ron as given me a solution.
Thanks for your help.

Mal
"Don Guillett" wrote in message
...
On Dec 12, 6:52 pm, "Mal" wrote:
Thanks Gord.
Both formulars return "6" but if you change the values in A2:F2, the
result
is still 6.
e.g. If you change C2 from 9 to 1 the answer should be 9 but in my
spreadsheet it still shows as 6.
I think the formular may just be adding column J or K.
Hope you can help.

Mal

"Gord Dibben" wrote in message

...







Please explain what "doesn't work" means.

Errors? Nothing? Not 6?

Using either of Don's formulas in G2 returns 6 for me using your
described data layout.

Gord

On Tue, 13 Dec 2011 08:58:40 +1000, "Mal"
wrote:

Don,
Thanks for your reply but I can't get either of your formulars to work.
Have you actually had the formulars running in the spreadsheet?
I have spent a couple of hours checking but cannot see what is wrong.
Would you chack and let me know as soon as possible.
Thanks,
Mal

"Mal" wrote in message
ond.com...
I have a row of data (only six items).

A B C D E F
1 3 9 4 2 8

A table of two columns
J K
1 3
2 2
3 1
=4 0

I want to look up each of the values in A to F, assign a value from
column K and Sum the results at G2

So values would be 3,1,0,0,2,0 and the Sum in G2 = 6

I have tried SUM(LOOKUP(A2:F2,J2:J4)) but end up with a #VALUE!

Any help appreciated.

Thanks,
Mal

I just re-tested and find that, using the second formula, if you
change c2 to 9 you still get 6 because your numbers still have
1,2,3......Try changing e2