Conditional Format MATCH

[surkdidat]

Hi all

I have the below formula, which works "perfectly", apart from the last part of "I2="ABCDE"

The formula highlights the values in my master sheet if C2 and E2 are not in a row in Sheet6 in Columns D and Column F respectively.

However, I only want the criteria to highlight the cell, if on my master sheet in I2 is ABCDE.

Any other value ion this column, I want it to ignore the rule.

=ISERROR(AND(MATCH(C2,'SHEET6'!$D:$D,0),MATCH(E2,'SHEET6'!$F:$F,0),I2="ABCDE"))

 

[Fluff]

In what way doesn't it work?

 

[surkdidat]

In what way doesn't it work?

It ignores what is in column I. So, whether that value be ABCDE, or DEFGH it will still highlight the cell if the first two parts of the conditional format are correct.
In other words if I deleted the i2=ABCDE, it would do the same without it

I hope that makes sense

 

[Fluff]

That's because you are using iserror & I doubt you formula is working at all.
The only time the cell will NOT be highlighted is when the 1st two conditions are TRUE.
Also I would recommend using countif rather than match

=AND(COUNTIF(SHEET6!$D:$D,C2),COUNTIF(SHEET6!$F:$F,E2),I2="ABCDE")

 

[surkdidat]

That's because you are using iserror & I doubt you formula is working at all.
The only time the cell will NOT be highlighted is when the 1st two conditions are TRUE.
Also I would recommend using countif rather than match

=AND(COUNTIF(SHEET6!$D:$D,C2),COUNTIF(SHEET6!$F:$F,E2),I2="ABCDE")

Hi Fluff

Thank you - I don't think I explained very well, as I wanted it to do the reverse, highlight when NOT true.

But, the coding you have provided has helped me get the solution, so thank you!

 

[Fluff]

Glad to help & thanks for the feedback.