+ Reply to Thread
Results 1 to 13 of 13

Sum multiple Index Match Lookups!

  1. #1
    Registered User
    Join Date
    02-06-2015
    Location
    ny
    MS-Off Ver
    excel 2010
    Posts
    36

    Sum multiple Index Match Lookups!

    This one should be an easy one!
    I have a file that gives me a cross match based on style and month.
    However, if the user chooses say, March for example, I want the formula to add January and February as well.
    I know there is a along complicated way to do this where I add Index and Matches and maybe several nested if-thens but if I could get one formula, that would be great!

    So Basically instead of =index(a1:z1000,match(Style,a1:a1000,0),match("Jan",a1:z1,0) + index(a1:z1000,match(Style,a1:a1000,0),match("Feb",a1:z1,0)
    have it say something like
    =index(a1:z1000,match(Style,a1:a1000,0),match({"Jan","Feb"},a1:z1,0).
    Thanks

  2. #2
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,907

    Re: Sum multiple Index Match Lookups!

    Can you upload a sample workbook?
    Spread the love, add to the Rep

    "None of us are as smart as all of us."

  3. #3
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Sum multiple Index Match Lookups!

    Hi.

    Please take a few moments to re-read the forum rules, in particular that which relates to cross-posting.

    http://www.mrexcel.com/forum/excel-q...h-lookups.html

    http://www.ozgrid.com/forum/showthread.php?t=193152

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  4. #4
    Registered User
    Join Date
    02-06-2015
    Location
    ny
    MS-Off Ver
    excel 2010
    Posts
    36

    Re: Sum multiple Index Match Lookups!

    Book1.xlsx
    sorry here you are

  5. #5
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,907

    Re: Sum multiple Index Match Lookups!

    Would something like this work:

    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    02-06-2015
    Location
    ny
    MS-Off Ver
    excel 2010
    Posts
    36

    Re: Sum multiple Index Match Lookups!

    in theory, but actually the equation is more than just months taht it looksup, it is actually a month and another value that it looks up, for example, it is actually not just january but "januaryunit", "Januarycost", so the lookup value horizontily is not just a month.

  7. #7
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,907

    Re: Sum multiple Index Match Lookups!

    We need your data, exactly how it is setup, to be able to assist you.

  8. #8
    Registered User
    Join Date
    02-06-2015
    Location
    ny
    MS-Off Ver
    excel 2010
    Posts
    36

    Re: Sum multiple Index Match Lookups!

    Book1-3.xlsx
    Sorry
    try this

  9. #9
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,907

    Re: Sum multiple Index Match Lookups!

    Assuming the values in row 2 are only there for the INDEX/MATCH, and assuming you won't be using that approach anymore, can I alter that data?

  10. #10
    Registered User
    Join Date
    02-06-2015
    Location
    ny
    MS-Off Ver
    excel 2010
    Posts
    36

    Re: Sum multiple Index Match Lookups!

    Quote Originally Posted by mcmahobt View Post
    Assuming the values in row 2 are only there for the INDEX/MATCH, and assuming you won't be using that approach anymore, can I alter that data?
    Yes you can my friend

  11. #11
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,907

    Re: Sum multiple Index Match Lookups!

    Will something like this work then? You can hide row 2 for formatting purposes as well.
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    02-06-2015
    Location
    ny
    MS-Off Ver
    excel 2010
    Posts
    36

    Re: Sum multiple Index Match Lookups!

    I will try that, in the meantime, I have found a way I like but the sumproduct formula keeps giving me errors cause the sheet has alot of errors and blanks and texts
    how can I make this formula to ignore all those errors and blanks in the data array without having to manually change data to suit the formula.
    =SUMPRODUCT(('2015OTB'!K16:N23)*('2015OTB'!A16:A23=A16)*(ISNUMBER(MATCH(K2:N2,A1:A2,0)))*(ISNUMBER(MATCH(K14:N14,A3:A4,0))))

    thanks

  13. #13
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,907

    Re: Sum multiple Index Match Lookups!

    I'd have to see how your data is setup to determine what kind of errors and blanks the cells contain. Can you upload a sample workbook?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Index and Match where one of the lookups needs to be nearest to
    By Alkaline198 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 08-21-2014, 04:35 PM
  2. [SOLVED] Multiple criteria lookups (INDEX + MATCH?)
    By exceldummy1990 in forum Excel Formulas & Functions
    Replies: 20
    Last Post: 08-12-2014, 07:20 AM
  3. Index Match formula for multiple lookups
    By RequestGuruHelp in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 06-04-2013, 10:51 PM
  4. INDEX and MATCH with range LOOKUPs?
    By neilbomb in forum Excel General
    Replies: 9
    Last Post: 06-22-2010, 07:33 PM
  5. Lookups Match Index??
    By myheadhurts in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-31-2008, 03:27 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1