I have a column/range of emails like:

and a column/range of names like:

  • jschmoe
  • vwilliams
  • tbrady
  • rdowneyjr
  • caguilera
  • swilliams

I'm trying highlight the emails column based on if it finds any substring name from the names column in any of the email columns' emails.

I've tried various variations of COUNTIF, but most of the examples I have found online are for excel and not specifically google sheets.

I've tried =COUNTIF(INDIRECT("ScrubbedNames"), I4) and =COUNTIF(INDIRECT("ScrubbedNames"), I4:I159)

Where ScrubbedNames is the range of names without the email domain and I4 is the first email on the list, or I4:I159 is the entire list of emails

Any help with this would be greatly appreciated as I am new to Stack Exchange and Conditional Formatting in Google Sheets.


1 Answer 1


The countif() function will by default do an exact match, and jschmoe does not exactly match [email protected].

To get partial matches, use the * wildcard. In your use case, you apparently want to match usernames like jschmoe with email addresses like [email protected]. You can do that like this:

=countif(indirect("ScrubbedNames"), I4 & "@*")

The ScrubbedNames named range is wrapped in indirect(). That is only required because this is a conditional formula custom formula rule rather than a regular formula.

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.