Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Sumproduct for Vlookup within Vlookup?

  1. #1
    New Member
    Join Date
    Jun 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

  2. #2
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    18,728
    Post Thanks / Like
    Mentioned
    29 Post(s)
    Tagged
    5 Thread(s)

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

  3. #3
    New Member
    Join Date
    Jun 2011
    Posts
    41
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sumproduct for Vlookup within Vlookup?

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

  4. #4
    New Member
    Join Date
    Jun 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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

  5. #5
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    18,728
    Post Thanks / Like
    Mentioned
    29 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Sumproduct for Vlookup within Vlookup?

    You're very welcome!

    Cheers!

  6. #6
    New Member
    Join Date
    Jun 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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)}

  7. #7
    New Member
    Join Date
    Jun 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

  8. #8
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    18,728
    Post Thanks / Like
    Mentioned
    29 Post(s)
    Tagged
    5 Thread(s)

    Default 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.
    Last edited by Domenic; Jun 2nd, 2019 at 10:26 AM.

  9. #9
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    18,728
    Post Thanks / Like
    Mentioned
    29 Post(s)
    Tagged
    5 Thread(s)

    Default 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.
    Last edited by Domenic; Jun 2nd, 2019 at 11:02 AM.

  10. #10
    New Member
    Join Date
    Jun 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sumproduct for Vlookup within Vlookup?

    Thanks a lot, this will help huge in the future!

    BR,
    Vitja

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •