Conditional Formatting w/multiple conditions and partial text match

Question

I am working in google sheets and have a manual input quantity in column E. I am trying to write a conditional format that will highlight each cell in column E (or the whole row) that is greater than 1 only if column C's matching row begins with "EM". I have a few variations but this is closest that could seem to work but cells that do not match the conditions are getting highlighted and cells with the conditions are being omitted...

=and(regexmatch(C2,"EM"),E2>1)

I know I will need some wildcards in there but I tried a few different combo's already with no luck. Thanks for any help!

Answer 1

cells that do not match the conditions are getting highlighted and cells with the conditions are being omitted

That sound like you need a NOT to negate the result.

column C's matching row begins with "EM"

The regular expression now matches all content which contains EM, so you probably need to replace the argument with "^EM"; the caret indicates the beginning of the content. (That's a standard regex operator; as indicated here, Google Sheets uses the RE2 version.)

You could try this:

=not(and(regexmatch(C2,"^EM"),E2>1))

and see whether it works for you.