Excel conditional formatting with two NOT functions - NOT Match and NOT Blank

Question

I essentially need help making a formula that highlights a cell when the cell isn't blank, and there aren't any matches in two specific columns. My current formula that isn't working is: =IF(AND(NOT(MATCH(G1,AC:AC,0))),NOT(ISBLANK($H5)))

For context: I have an Excel log where people write in if they have sold an item with a specific serial number. I also have a generated report from the computer that tells me if they ran it through our inventory system when they got back in the office.

To make sure the log was being completed, I made two conditional formatting rules:

1. if a cell is not blank, it remains with normal formatting
2. if a cell has a match, it is highlighted, using this formula: =MATCH(G1,AC:AC,0)

With those two rules in that order it has worked to show me when they accidentally forgot to log their sale. However, we had a few people accidentally log in that they sold a product number that they did not, ie. they were off a row so it looked like they sold a different number than they did.
How do I fix this?

Answer 1

For complex conditions you should implement it as a formula in another column, then use a conditional formatting based on that formula.

As a fail-safe, you should ensure your additional formula-column defaults to a non-0 and non-empty result, so that when formula is absent your conditional formatting can alert.