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
    Aug 30 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
    Sep 1 at 16:15
  • It's a formula! Formula starts with = sign: change to =(COUNTIF($B$3:$B$8,C3)=0) :-)
    – JohnSUN
    Sep 1 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
    Sep 1 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, privacy policy and cookie policy

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