Simplification of summing multiple vlookups together

[Stuart Little]

Hi All,

I currently have the following formula in a spreadsheet which has 600 rows in Excel 2010.

=SUM($O7*VLOOKUP($P7,pts,F$5,0)+$Q7*VLOOKUP($R7,pts,F$5,0))+$S7*VLOOKUP($T7,pts,F$5,0)+$U7*VLOOKUP($V7,pts,F$5,0)+$W7*VLOOKUP($X7,pts,F$5,0)+$Y7*VLOOKUP($Z7,pts,F$5,0)+$AA7*VLOOKUP($AB7,pts,F$5,0)+$AC7*VLOOKUP($AD7,pts,F$5,0)+$AE7*VLOOKUP($AF7,pts,F$5,0)+$AG7*VLOOKUP($AH7,pts,F$5,0))

Is there an easier formula?

Code Breakdown
SUM($O7*VLOOKUP($P7,pts,F$5,0)

O7 = Qty
P7 = Part Number
Pts = table with Usage volumes
F5 = just a cell with column I want to bring back as I drag this formula across several columns

Any simplification would be great as the formula has the potential to grow and already includes 10 variations.

Thank you in advance.

 

[VBA Geek]

can't test this but see if it works...

=SUMPRODUCT(OFFSET(O7,,{0,2,4,6,8,10,12,14,16,18}),INDEX(pts,N(IF(1,MATCH(OFFSET(p7,,{0,2,4,6,8,10,12,14,16,18}),INDEX(pts,0,f$5),0))),f$5))

 

[Stuart Little]

can't test this but see if it works...

=SUMPRODUCT(OFFSET(O7,,{0,2,4,6,8,10,12,14,16,18}),INDEX(pts,N(IF(1,MATCH(OFFSET(p7,,{0,2,4,6,8,10,12,14,16,18}),INDEX(pts,0,f$5),0))),f$5))

Hi,

Thank you for the reply the code brings back a #Value

I had originally thought of a sumproduct formula but not incorporating the offset, index and match

 

[XOR LX]

I believe VBA Geek's formula should have contained some deferencers, i.e. (assuming the values in P7, R7, T7, V7, X7, Z7, AB7, AD7, AF7 and AH7 are not numeric):

=SUMPRODUCT(N(OFFSET(O7,,{0,2,4,6,8,10,12,14,16,18})),INDEX(pts,N(IF(1,MATCH(T(OFFSET(P7,,{0,2,4,6,8,10,12,14,16,18})),INDEX(pts,0,1),0))),F$5))

You can also use VLOOKUP:

=SUMPRODUCT(N(OFFSET(O7,,{0,2,4,6,8,10,12,14,16,18})),VLOOKUP(T(IF(1,T(OFFSET(P7,,{0,2,4,6,8,10,12,14,16,18})))),pts,F$5,0))

My preference would be to remove the volatile functions altogether and make the construction more flexible (i.e. it does not contain static arrays) by using an array formula**:

=SUM(IF(ISEVEN((COLUMN(O7:AG7)-MIN(COLUMN(O7:AH7)))),O7:AG7*VLOOKUP(T(IF({1},P7:AH7)),pts,F$5,0)))

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).