Formula that will LOOKUP / INDEX 3 criteria then extract over result if MATCH found

    I'm having a problem finishing this formula to do what I want... (was going to do array formula but open to using anything that works!)
    =MATCH(1,(droptableNEW=O21)*(droptableNEW=P21)*(droptableNEW=Q21),0)


    I've attch'd a small sample file to make it easy to understand using colors to show -- what should feed to where..


    Need formula for G21
    I tried to start with the following (CTRL+SHIFT+ENTER) array formula but not sure how to finish it off..


    =MATCH(1,(droptableNEW=O21)*(droptableNEW=P21)*(droptableNEW=Q21),0)


    First it looks to O21 for the Month, then P21 for Year, then Q for Type, then
    it should go to the "tables" sheet, within the named range table called: "droptableNEW" to locate those matches...


    Once it finds all 3 matches, it should extract over the result found in the Q column of the "tables" sheet, from the NAMED RANGE called: "droptableNEW" and paste it into G21 of the SUMMARY tabforum.ozgrid.com/index.php?attachment/63789/

    Re: Formula that will LOOKUP / INDEX 3 criteria then extract over result if MATCH fou


    You can't using MATCH function with named ranges in all columns, try to split named ranges per column


    cheers

    Re: Formula that will LOOKUP / INDEX 3 criteria then extract over result if MATCH fou


    Do you mean?


    [COLOR="#0000FF"]=INDEX(INDEX(droptableNEW,0,4),MATCH(1,(INDEX(droptableNEW,0,1)=O21)*(INDEX(droptableNEW,0,2)=P21)*(INDEX(droptableNEW,0,3)=Q21),0))[/COLOR]


    [COLOR="#0000FF"]=INDEX(INDEX(droptableNEW,0,6),MATCH(1,(INDEX(droptableNEW,0,1)=O22)*(INDEX(droptableNEW,0,2)=P22)*(INDEX(droptableNEW,0,5)=Q22),0))[/COLOR]


    and


    [COLOR="#0000FF"]=INDEX(INDEX(droptableNEW,0,8),MATCH(1,(INDEX(droptableNEW,0,1)=O23)*(INDEX(droptableNEW,0,2)=P23)*(INDEX(droptableNEW,0,7)=Q23),0))[/COLOR]


    respectively?


    All confirmed with CSE key combo.

    Where there is a will there are many ways. Finding one that works for you is the challenge!

    Re: Formula that will LOOKUP / INDEX 3 criteria then extract over result if MATCH fou


    That's it!!! All three of those work PERFECTLY NBVC!! You're awesome! Much appreciated!
    The results now appear appropriately on the SUMMARY sheet:
    "35" in the orange block "#DDR Rec'd"
    "48" in the green block "Completed DDR"
    "-13" in the IN PROCESS block
    "2" in the yellow "Avg Completion Days" block

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login