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 preferably a 0 ("") or 1 output, if any of the conditions are met:
If cell A7 is blank (or empty) / can also be if cell B2 contains part of the text string "No match"
If cell H7 is blank (or empty) / can also be if cell J2 contains part of the text string "No match"
If cell A7 does not match any value in Column H
If cell H7 does not match any value in Column I
I circled cells A17 and H36 in hopes that a single formula will trap, or identify, these types of anomalies in Column I with a 0 for non-matches.
Attached is a copy of the current spreadsheet. Any help is greatly appreciate. Best regards.
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.
