Simplification of summing multiple vlookups together

Stuart Little

Board Regular
Joined
Mar 4, 2002
Messages
64
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.
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

VBA Geek

MrExcel MVP
Joined
Dec 16, 2013
Messages
2,857
can't test this but see if it works...

Code:
=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))
 
Upvote 0

Stuart Little

Board Regular
Joined
Mar 4, 2002
Messages
64
can't test this but see if it works...

Code:
=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
 
Upvote 0

XOR LX

Well-known Member
Joined
Jul 2, 2012
Messages
4,517
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).
 
Upvote 0

Forum statistics

Threads
1,195,668
Messages
6,011,056
Members
441,580
Latest member
BornholmerBjarne

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top