Simplification of summing multiple vlookups together

Stuart Little

Board Regular
Hi All,

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

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.

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
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))``

Stuart Little

Board Regular
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

XOR LX

Well-known Member
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).

Replies
4
Views
673
Replies
2
Views
597
Replies
5
Views
684
Replies
1
Views
436
Replies
0
Views
246

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.

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

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