On Mon, 12 Dec 2011 17:48:04 +1000, "Mal" wrote:

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


Assuming that your A:F data is in Row 2; and that your J:K data starts in Row 1

Change J4 from =4 to just the number 4

Then enter this formula in G2:

=SUMPRODUCT(N(OFFSET(K1,MATCH(A2:F2,J1:J4)-1,0)))