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