0

I have a column/range of emails like:

  • jschmoe@zzz.org
  • vwilliams@zzz.org
  • hpotter@zzz.org
  • swilliams@zzz.org

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
0

The countif() function will by default do an exact match, and jschmoe does not exactly match jschmoe@zzz.org.

To get partial matches, use the * wildcard. In your use case, you apparently want to match usernames like jsmith with email addresses like jsmith@example.org. You can do that like this:

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

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.