CODEWITHSUNDEEP
if-statementgoogle-sheetsconditional-statementsmatchconditional-formatting
JoeW
JoeW

Reputation: 147

conditional formatting checking for a match in two different columns

I'm trying to make a conditional formatting rule that says: IF A1 MATCHES a date in row B or C AND is greater than D1 I can do this with two conditional rules:

=AND(match(A1, $B:$B,0),A1>=$D$1)
=AND(match(A1, $C:$C,0),A1>=$D$1)

but it doesnt work when I try to check both match statements in one conditon:

=AND(OR(match(A1, $B:$B,0),match(A1, $C:$C,0)),A1>=$D$1)

and neither does

=AND(match(A1, $B:$C,0),A1>=$D$1)

I need to do this a lot and writing them all seperately will take about a hundred CF's so was wondering if anyone knows a way of checking all these conditions in one?

EDIT:

I've made a simple example: https://docs.google.com/spreadsheets/d/1UqIZuJQviIgk4mjtBj8yH0_FFJavCrhcRdMB4kV8h04/edit?usp=sharing In this example, A2 will only go green if these conditions are all true:

=AND(match(A2, $B2:$B,0),A2>=$D$2,A2<$E$2)
A2 also appears in column B
A2 is after D2
A2 is before E2

What I want is one conditional statement that checks these conditions but rather than just seeing if the date matches a date in column B, see if the date matches a date in column B OR column C.

Upvotes: 1

Views: 82

Answers (2)

player0
player0

Reputation: 1

try:

=MATCH($A$2, {$B$2:$B; $C$2:$C}, 0)*($A$2>=$D$2)*($A$2<$E$2)

Upvotes: 2

iansedano
iansedano

Reputation: 6481

How to combine simple logical comparison and multiple MATCH instances

=AND(
    OR(
        IFERROR(MATCH(A2,$B2:$B,0), FALSE),
        IFERROR(MATCH(A2,$C2:$C,0), FALSE)
    ),
    A2>=$D$2,
    A2<$E$2
)

If MATCH doesn't find anything, then it returns an error. So you need to wrap it in an IFERROR or similar. Omitting it works for one, but when you wrap them together in an OR it looks like it will always end up returning FALSE because it will always return an ERROR.

Reference

Upvotes: 1

Related Questions