# Sumproduct for Vlookup within Vlookup?

#### Vitja

##### New Member
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

### Excel Facts

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

#### Domenic

##### MrExcel MVP
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!

• Vitja

#### Roncondor

##### Board Regular
FYI - In general, you should always use INDEX with MATCH instead of VLOOKUP

#### Vitja

##### New Member
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
You're very welcome!

Cheers!

#### Vitja

##### New Member
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
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
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
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:

#### Vitja

##### New Member
Thanks a lot, this will help huge in the future! BR,
Vitja