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
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,179
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!
 

Roncondor

Board Regular
Joined
Jun 3, 2011
Messages
52
FYI - In general, you should always use INDEX with MATCH instead of VLOOKUP
 

Vitja

New Member
Joined
Jun 1, 2019
Messages
5
Thanks Domenic, it works! :)
Yes, Roncondor, I just recently found out that INDEX/MATCH is more powerfull than VLOOKUP, still learning these :)

BR,
Vitja
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,179
You're very welcome!

Cheers!
 

Vitja

New Member
Joined
Jun 1, 2019
Messages
5
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)}
 

Vitja

New Member
Joined
Jun 1, 2019
Messages
5
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 :))
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,179
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:

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,179
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:

Watch MrExcel Video

Forum statistics

Threads
1,099,754
Messages
5,470,574
Members
406,707
Latest member
drkjz

This Week's Hot Topics

Top