Excluding 0 from Conditional Formatting

[bearcub]

I have the following formula:

=INDEX(client_CTA_co_organization_regi!$S$2:$S30000,MATCH($B2&$C2,client_CTA_co_organization_regi!$B$2:$B30000&client_CTA_co_organization_regi!$C$2:$C30000,0))

It looks compares an email field between the client_CTA sheet and the email field in column J.


I have a conditional formatting rule that says if the Col J and S on the client_CTA sheet don't match highlight the cell red.

The rule works fine except in some instance both the email field and the client_CTA document are empty (neither have an email address in them).

In those cases where both cells are blank I get a zero value and the cell is highlighted red. I would like to exclude this value from the rule. I've tried using Len, IsBlank, W2 = 0 (column W is where I have the formula), W2 = 0. Nothing seems to work. Am I doing something wrong and is there a formula that I could use to create another rule to exclude 0 from the rule?

Thank you for your help.

Michael

 

[jtakw]

OK, so the 0 is in W2?, how does that 0 get there, by a formula I'm guessing, please show That formula, I'm suspecting a TEXT version of 0

 

[bearcub]

Here is the formula, Index Match. The criteria is using the first and last name fields to pull the email address. The conditional formula has the 3 conditions, 2 which work and the 3rd one that doesn't.

=INDEX(client_CTA_co_organization_regi!$S$2:$S30000,MATCH($B2&$C2,client_CTA_co_organization_regi!$B$2:$B30000&client_CTA_co_organization_regi!$C$2:$C30000,0))

i would thought, like you, that I am returning a text version of zero but when I run the W2= 0 I get a true and when I run W2 = "" I get a false. Column S in the formula is the email address field

Michael