Forum Discussion
Need help with a formula that matches multiple criteria contained in 4 separate columns/cell
I'm working on the same spreadsheet from over the weekend. This time I'm trying to come up with a single formula in cell I7, that can be copied down, to capture the following 4 criteria, with prefer...
Hi Kelly,
Sorry, it was misprint in second COUNTIF, the formula is
=IF(OR(OR(ISBLANK($A7),ISNUMBER(SEARCH("no match",$B$2))),OR(ISBLANK($H7),ISNUMBER(SEARCH("no match",$J$2))),COUNTIF(H:H,A7)=0,COUNTIF(A:A,H7)=0),0,"match")and in attached. In row 36 is 0 since A36 is empty.
Hi Kelly,
Perhaps like
=IF(OR(OR(ISBLANK($A7),ISNUMBER(SEARCH("no match",$B$2))),OR(ISBLANK($H7),ISNUMBER(SEARCH("no match",$J$2))),COUNTIF(H:H,A7)=0,COUNTIF(H7,A:A)=0),0,"match")
in your case always returns 0 since $J$2 contains "no match"
Thank you very much Sergie. I pasted the formula in cell I7 and get a match in cell I7, but zeros all the rest of the way down. Would you happen to know if I need to alter or modify the formula in any way?
Best regards, Kelly
Hi Kelly,
Sorry, it was misprint in second COUNTIF, the formula is
=IF(OR(OR(ISBLANK($A7),ISNUMBER(SEARCH("no match",$B$2))),OR(ISBLANK($H7),ISNUMBER(SEARCH("no match",$J$2))),COUNTIF(H:H,A7)=0,COUNTIF(A:A,H7)=0),0,"match")and in attached. In row 36 is 0 since A36 is empty.
Thank you Sergie, it worked perfectly. I greatly appreciate the help/support.
