Conditional Formating - formula not working

[EagleOne]

2003

Cell A1 = 101
Cell B1 = 101
Cell C1 = #N/A (Unsuccessful VLookup)

What I want (CF background color of A1 = Red) if EITHER B1 or C1 does
not equal A1

The actual not-working formula in A1 Conditional Format is:
Formula is =OR(A1<>B1,A1<>C1) Then Set Background Color to Red

Thanks EagleOne

 

[Guest]

Both B1 and C1 are not equal to A1 in the example you gave. Therefore the
conditional formatting is not applied.

 

[Guest]

Why not just clean up your vlookup formula so that it no longer returns #NA.
You can do that with an if statement and either CountIf or ISNA something
like this...

=if(countif(A2:A10, A1)= 0, "Not found", vlookup(A1, A2:B10, 2, False))
or
=if(ISNA(vlookup(A1, A2:B10, 2, False)), "Not Found", vlookup(A1, A2:B10, 2,
False))

 

[EagleOne]

OK, humor me.

A1 = B1
A1 <> C1

Since this is OR, if either (not both) conditions do not equal A1 then
the Format should be applied. No? Maybe the formulas work differently
when in CF?

 

[Guest]

Using your logic I got the red to work - however I had to remove the double
quotes the conditional formating added when I typed in the equation

 

[EagleOne]

Your point is well taken. Even so, I still have the CF issue meaning
that A1 <> C1 whether it is #N/A or "Not Found." Did I miss something?

 

[EagleOne]

Brad,

The issue appears to be related to the displayed results of an
unsuccessful VLookup. Meaning, if I type in #N/A, the CF formula works
but if the displayed #N/A is from the VLookup, it is not considered
"not equal" and the formula fails.

How do I work around this?

EagleOne

 

[EagleOne]

In the end, your suggestion worked because "Not Found" tripped the CF
formula properly whereas the displayed #N/A from the VLookup did not.
Thanks Jim but .....

Do you or does anyone know WHY the formula does not work in that case?

EagleOne

 

[Herbert Seidenberg]

Convert the #N/A into a number that is never equal to A, say 0,
with this formula:
=IF(ISERROR(Cm),0,Cm)
Then plug it into your OR formula like this:
=OR(A=B,IF(ISERROR(C),0,C)<>A)
This is an inclusive OR, defined as "if either term is TRUE",
but you specified: "if either term is TRUE, but not both".
This is called an exclusive OR and is much more complicated.
Google "exclusive OR"
The formula then becomes:
=OR((A=B)*NOT(IF(ISERROR(C),0,C)<>A),
NOT(A=B)*(IF(ISERROR(C),0,C)<>A))

 

[Guest]

My suggestion would have been the same as Jim's

=if(ISNA(vlookup(A1, A2:B10, 2, False)), "Not Found", vlookup(A1, A2:B10, 2,
False))
--

With one small adjustment
=if(ISNA(vlookup(A1, $A$2:$B$10, 2, False)), "Not Found", vlookup(A1,
$A$2:$B$10, 2,
False))

That way if you need to copy the equation - your lookup range remains the
same.

Why Excel handled the #NA differently - can't give you a 100% definitive
answer.

 

[EagleOne]

Even -OR- is argumentative! (no wonder learning this is challenging).
Thanks for the insight

 

[EagleOne]

Brad, thanks for your time and knowledge!

My suggestion would have been the same as Jim's

=if(ISNA(vlookup(A1, A2:B10, 2, False)), "Not Found", vlookup(A1, A2:B10, 2,
False))
--

With one small adjustment
=if(ISNA(vlookup(A1, $A$2:$B$10, 2, False)), "Not Found", vlookup(A1,
$A$2:$B$10, 2,
False))

That way if you need to copy the equation - your lookup range remains the
same.

Why Excel handled the #NA differently - can't give you a 100% definitive
answer.