I am new to Google Sheets and trying to create a custom formula in conditional formatting. Looking to see if someone would be kind enough to help.

The picture below is a sample size of two list of Serial numbers. What I need is the cells to change color if a match is detected. What I was doing before was putting both list under one column and using the formula =countif(B:B,B1)>1. But this formula no longer works for me because now my list have cells that contain more than 1 serial number. enter image description here So what I need help with making a formula that would change the color of the cells if anything in C column is found in the B column. But for cells with more than one serial number, the cell in Column B would only change once all serial numbers on the cell were matched. I'm basically trying to verify that all Serial numbers on column C are in column B. But i dont know how to go about it or cells with more than one S/N.

This sounds like a super complicated task. I dont know if i should be asking here. If im at the wrong place please point me where to go

1 Answer 1


use find insteand of countif


Find returns how many times it finds a string in the text or cell value

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.