# Thread: Sumproduct for Vlookup within Vlookup? Thanks:  1 Post #5286969 (1) Likes: 0

1. ## Sumproduct for Vlookup within Vlookup?

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)+
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?

Vitja  Reply With Quote

2. ## Re: Sumproduct for Vlookup within Vlookup?

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!  Reply With Quote

3. ## Re: Sumproduct for Vlookup within Vlookup?

FYI - In general, you should always use INDEX with MATCH instead of VLOOKUP  Reply With Quote

4. ## Re: Sumproduct for Vlookup within Vlookup?

Thanks Domenic, it works! Yes, Roncondor, I just recently found out that INDEX/MATCH is more powerfull than VLOOKUP, still learning these BR,
Vitja  Reply With Quote

5. ## Re: Sumproduct for Vlookup within Vlookup?

You're very welcome!

Cheers!  Reply With Quote

6. ## Re: Sumproduct for Vlookup within Vlookup?

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)}  Reply With Quote

7. ## Re: Sumproduct for Vlookup within Vlookup?

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 )  Reply With Quote

8. ## Re: Sumproduct for Vlookup within Vlookup?

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.  Reply With Quote

9. ## Re: Sumproduct for Vlookup within Vlookup?

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.  Reply With Quote

10. ## Re: Sumproduct for Vlookup within Vlookup?

Thanks a lot, this will help huge in the future! BR,
Vitja  Reply With Quote

## User Tag List

sumproduct, vlookup 