LinkBack URL
About LinkBacks
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.
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.
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.
adjust the ranges to suit your data.
Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
Sam Capricci
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
...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.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks