I am looking to compare two columns, a "Roster" column and a "Present" column and highlight any missing values.

The "Roster" column is a fixed value, but the "Present" column is a formula matching a name to an attendance code.

I have tried countif, unique, match, and pretty much every other type of formula to make this work, but I cannot get it working properly. I have attached a sample worksheet below, any help would be GREATLY appreciated!


(I have this working in Excel with Conditional Formatting and Unique Values but Google Sheets is another beast).

  • Welcome! Please try Conditional Format for range C3:C8 with custom formula =(COUNTIF($B$3:$B$8,C3)=0)
    – JohnSUN
    Commented Aug 30, 2022 at 19:08
  • Thank you so much for the suggestion! I am unable to get the formula to produce the formatting, and have attached a screenshot below. I'm frustrated since this should be so simple! If need be I am willing to allow access to this sheet for any troubleshooting. Cheers! drive.google.com/file/d/1Vpyk9VycUAb_9DADqUkU2AI77_E3mr_7/…
    – Jon
    Commented Sep 1, 2022 at 16:15
  • It's a formula! Formula starts with = sign: change to =(COUNTIF($B$3:$B$8,C3)=0) :-)
    – JohnSUN
    Commented Sep 1, 2022 at 17:19
  • Oh. My. Gosh. I cannot believe this. The first time I added in the = sign it gave me an error so I figured I did something wrong. I go back and add it now, realizing I missed the last closed paranthesis. I cannot thank you enough, Cheers mate!
    – Jon
    Commented Sep 1, 2022 at 17:34

1 Answer 1



You can use match to look at the list of scanned numbers and color the roster names if their id number is not matched.


Conditional Formatting Custom Formula:

    =IFERROR(MATCH($D3,$A$3:$A$26,0)<1,TRUE)   // TRUE if no match 

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.