Index Match or Similar Across Multiple Criteria

  • Item: Value in (DC#1): Value in (DC#2):
    510 lookupformula#1 lookupformula#2


    This information is on tab A of the worksheet.


    Item: DC Value: Type: Amount:
    510 DC1 Gross 10
    510 DC1 Change -5
    510 DC1 Net 5
    510 DC2 Gross 15
    510 DC2 Change -5
    510 DC2 Net 10


    This is the information on tab B of the worksheet.


    What I am wanting in lookupformula#1 is a formula that will evalute tab b for... Item 510, DC1, Net. Thus, the value of lookupformula#1 would be 5. Further, the value of lookupformula#2 would be 10.


    I hope that this is enough information and that I have made myself clear. This is my first time on the forum and after many hours of googling and index matching with no success this is my last hope. Thanks everyone!

  • Re: Index Match or Similar Across Multiple Criteria


    Hi djmoly


    Welcome to the forum.


    I am not quite clear about question. Assuming that you need to sum the net value - if the conditions you mentioned are met. If so, the following formulas will do it. If not please attach a sample workbook with expected results.


    [F]
    =SUMIFS(Sheet1!$D$2:$D$25,Sheet1!$A$2:$A$25,A2,Sheet1!$B$2:$B$25,B2,Sheet1!$C$2:$C$25,C2)
    [/f]


    Or


    [F]
    =SUMPRODUCT((Sheet1!$A$2:$A$25=Sheet2!A2)*(Sheet1!$B$2:$B$25=Sheet2!B2)*(Sheet1!$C$2:$C$25=Sheet2!C2)*(Sheet1!$D$2:$D$25))
    [/F]


    Regards



    Maqbool

  • Re: Index Match or Similar Across Multiple Criteria


    Thank you so much! It seems like the easiest solutions are always the ones you don't try in these situations! Thanks again for the help!