I am trying to create a custom formula for conditional formatting. I want to highlight the cell if the text appears in a list of names. This list will contain 210 unique names so I can't type each one of those into the formula. I am hoping I can reference a list, but can't seem to find a example for Google Sheets. I was able to see an example for excel that would reference a "List" you would create.

Thanks, Noel

1 Answer 1


EDIT (following your comment)

All duplicate values highlighted in both columns is achieved using:

  • In range D3:D27: =COUNTIF($A$3:$A,D3)
  • In range A3:A27: =COUNTIF($D$3:$D,A3)

duplicates in both columns.

First approach

I believe the formula you need is as simple as: =$A1="Roman"

enter image description here

  • Isn't that only looking for one answer of "Roman", but what if the list also had Bob, Kirk, Rebecca + all the rest? I want to highlight everyone that appears in a second list.
    – Noel Basta
    Aug 16, 2019 at 4:41
  • Have you tried the updated solution offered? Did it work for you? If not we should try finding out why. If yes, as per site guidelines when an answer addresses your question, you should accept it so others can benefit as well -. (Why vote?) Aug 25, 2019 at 16:31

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.