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

## Files

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