# Looking to return multiple cell values match multiple criteria

1. ## Looking to return multiple cell values match multiple criteria

I'm looking for a formula to find multiple matches that match multiple criteria.

Attached is what I'm looking to do, in column G I'd like it to return anyone from Column C that has a zero is column D and where column A includes the number 47.  Register To Reply

2. ## Re: Looking to return multiple cell values match multiple criteria

If you don't mind a helper column, this in some column, for example E, =IF(AND(LEFT(A2,2)="47",D2=0),C2,"") dragged down.
then this in G =IFERROR(INDEX(\$E\$2:\$E\$12,MATCH(0,INDEX(COUNTIF(\$G\$1:G1,\$E\$2:\$E\$12&""),),0))&"","") dragged down until comes back blank.  Register To Reply

3. ## Re: Looking to return multiple cell values match multiple criteria

Another option without helper :

In G2, copied down :

=IFERROR(INDEX(C:C,AGGREGATE(15,6,ROW(A\$2:A\$12)/(IMREAL(A\$2:A\$12&"i")=47)/(D\$2:D\$12=0),ROW(A1))),"")

Regards
Bosco  Register To Reply

4. ## Re: Looking to return multiple cell values match multiple criteria

...or if you don't understand "complex numbers" (IMREAL)...

=IFERROR(INDEX(C:C,AGGREGATE(15,6,ROW(A\$2:A\$100)/(ISNUMBER(SEARCH(47,A\$2:A\$100)))/(D\$2:D\$100=0),ROWS(G\$2:G2))),"")

see sheet.  Register To Reply