Sumproduct for Vlookup within Vlookup?

Vitja

New Member
Joined
Jun 1, 2019
Messages
5
Hi,
I have a long formula with repeated sums where Vlookup column index is a result from other Vlookup formula:
=AB13*VLOOKUP($B13,Table4,VLOOKUP(AB$11,Table3,6,FALSE),TRUE)+
AC13*VLOOKUP($B13,Table4,VLOOKUP(AC$11,Table3,6,FALSE),TRUE)+
AD13*VLOOKUP($B13,Table4,VLOOKUP(AD$11,Table3,6,FALSE),TRUE)+
AE13*VLOOKUP($B13,Table4,VLOOKUP(AE$11,Table3,6,FALSE),TRUE)+
AF13*VLOOKUP($B13,Table4,VLOOKUP(AF$11,Table3,6,FALSE),TRUE)+
AG13*VLOOKUP($B13,Table4,VLOOKUP(AG$11,Table3,6,FALSE),TRUE)+
AH13*VLOOKUP($B13,Table4,VLOOKUP(AH$11,Table3,6,FALSE),TRUE)+
AI13*VLOOKUP($B13,Table4,VLOOKUP(AI$11,Table3,6,FALSE),TRUE)+
AJ13*VLOOKUP($B13,Table4,VLOOKUP(AJ$11,Table3,6,FALSE),TRUE)+
AK13*VLOOKUP($B13,Table4,VLOOKUP(AK$11,Table3,6,FALSE),TRUE)

I tried to simplify this formula with Sumproduct (actual formula is much more longer):
{=SUMPRODUCT(AB13:AK13*VLOOKUP(B13,Table4,VLOOKUP(AB11:AK11,Table3,6,FALSE),TRUE))}

but the second vlookup gives only AB11 result from Table3 row1 for each iteration.
Is there a mistake in my sumproduct formula, or it's not going to work this way, or maybe this can be solved with SUMIF instead of second Vlookup, or INDEX/MATCH formulas?

Thanks in advance,
Vitja
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Yes, try using SUMIF for the second VLOOKUP...

=SUMPRODUCT(AB13:AK13,VLOOKUP($B13,Table4,SUMIF(INDEX(Table3,0,1),AB$11:AK11,INDEX(Table3,0,6)),TRUE))

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!
 
Upvote 0
Thanks Domenic, it works! :)
Yes, Roncondor, I just recently found out that INDEX/MATCH is more powerfull than VLOOKUP, still learning these :)

BR,
Vitja
 
Upvote 0
Hmm.. turns out the Vlookup part still is not working because Table4 is NOT sorted ascending. Is there a way to fix this without sorting source table? It works if I sort the table, but it's not an option :(
And also I need this "TRUE" option from Vlookup to get closest match, since it's time values I work with here.

I tried to replace the first Vlookup with SUMIF/INDEX but it works only for exact match (and without Sumproduct):
=SUMIF(INDEX(Table4,0,1),B13,INDEX(Table4,0,SUMIF(INDEX(Table3,0,1),AB11,INDEX(Table3,0,6))))*AB12+
SUMIF(INDEX(Table4,0,1),B13,INDEX(Table4,0,SUMIF(INDEX(Table3,0,1),AC11,INDEX(Table3,0,6))))*AC12

Sumporduct like this takes only first sum twice:
{=SUMPRODUCT(SUMIF(INDEX(Table4,0,1),B13,INDEX(Table4,0,SUMIF(INDEX(Table3,0,1),AB11:AC11,INDEX(Table3,0,6))))*AB12:AC12)}
 
Upvote 0
Ok, I made macro that makes a copy of Table4 and sort it ascending. One Update button next to Table4 is easier for me then to fix this with formulas :))
 
Upvote 0
Best to sort your table, as you've decided to do. In case you're interested, though, the formula can be amended as follows...

=SUMPRODUCT(AB13:AK13,INDEX(Table4,MATCH(MIN(ABS(INDEX(Table4,0,1)-$B13)),ABS(INDEX(Table4,0,1)-$B13),0),N(IF(1,SUMIF(INDEX(Table3,0,1),AB$11:AK11,INDEX(Table3,0,6))))))

...confirmed with CONTROL+SHIFT+ENTER.
 
Last edited:
Upvote 0
Sorry, it actually finds the closest match on either side of the lookup value, not the closest match without going over, which is what you seem to want.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,528
Messages
6,114,154
Members
448,553
Latest member
slaytonpa

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
Back
Top