VLOOKUP inside SUMPRODUCT

[Elmo Scoggins]

I have been unable to come up with a solution for a complex Excel formula. I have a table for which I use SUMPRODUCT to multiply 2 columns in an array. However, I want to have the formula look at another column in the array, and based on that number do a HLOOKUP to another table, and them multiply the HLOOKUP result with the SUMPRODUCT results. The problem is the lookup needs to be done each time the SUMPRODUCT moves to the next row and I get a #VALUE error. Each part works by itself (=SUMPRODUCT((OFFSET(SDC_var_top:SDC_var_bottom,0,M$42)),((SDC_m2_top:SDC_m2_bottom))) & HLOOKUP((SDC_gen_top:SDC_gen_bottom),'Key Variables'!$B$80:$R$90,(YEAR(N$11)-2009)), with the SDC references being the 1st table, and the 'Key Variable' being the lookup table. SDC_var_top and SDC_var_bottom being the top and bottom reference points for the 1st table and SDC-m2_top and SDC_m2_bottom being the other multiplier in the 1st table, with SDC_gen_top & SDC--gen_bottom being another column in the 1st table, which becomes the lookup value for the lookup table.

All table references are numbers, as are all in the lookup table, other than the reference to YEAR(), which is a date that is converted to a number. Essentially the SUMPRODUCT pulls a number from the 1st table (3rd column) and converts it to a number in the lookup table, which it then multiplies with the other two columns in table 1 and sums the result.

I know its a bit obtuse but I would be happy to explain further if necessary...Any suggestions?