Forum Discussion
highlighting a row based on multiple piece of text (eg in a calendar of people's availability)
- =AND(MATCH("*xx*",$C2:$F2,0),MATCH("*family holiday*",$G2,0))
Here are the conditional formatting formulas:
1:
=MATCH("*xxx*",A$2,0)2.
=AND(MATCH("*xxx*",A$2,0),$G$2="family holiday")
Thanks for your response - I've made some slight changes and I am just about there!!!! 🙂
I have these two
somebody Unavailable:
=MATCH("*xx*",$C2,0)
(it WORKS!!! - thank you)
Conflict between C2 or D2 or E2 or F2 AND G2 contains a phrase somewhere in the cell then highlight (eg. "xx" AND "family holiday" exist)
=AND(MATCH("*xx*",$C2:$F2,0),$G2="family holiday")
The above works where G2 says exactly "family holiday" - I've tried amending to "*family holiday*" (eg. if the cell says "Family holiday to the coast" then I would still like that to trigger the rule - but when I add the wild card/asterisks the rule doesn't work anymore. Do you know how to tweak it?
- I didn't know family holiday was a partial match. You could use MATCH with the wildcard. Same idea as the "*xx*".
terribly sorry about that - I missed that bit in my original message.
I've tried:
=AND(MATCH("*xx*",$C2:$F2,0),$G2="*family holiday*")
and it seems to break the formula. If I remove those two wildcards then the formula works again but isn't a partial match which is ideally what I am after.
- =AND(MATCH("*xx*",$C2:$F2,0),MATCH("*family holiday*",$G2,0))
