On Sat, 14 Oct 2006 16:14:01 -0700, Shu of AZ
wrote:

Ron, thanks for helping with this formula but I still am not able to produce
the correct answer as seen in AB.

Cell AB59 has this formula in it =AND(OR(G59=K59:N59),OR(H59=K59:N59),1,0)

The logic is this. If G59 has a value found in one of the 4 cells K59:N59
and H59 has a value found in one of the 4 cells K59:N59 return the value of 1
in AB59. As you indicated I can conditionally format to return the 1 without
adding the ,1,0 but it seemed as it should have worked but you can see the
result.


AB

G59 H59 I59 J59 K59 L59 M59 N59 O59 P59 AB59
7 4 3 1 4 7 8 6 4 1 #VALUE!



Your formula will not work.

Your formula reduces to:

=AND(Condition1,Condition2,1,0)

It returns #VALUE! because you did not enter it as an **array** formula.

But even if you did, it would return FALSE since 1 & 0 cannot both be TRUE at
the same time.

This is what I wrote befo

You should be able to use the formula either to conditionally format, or if you
must have a one or zero, then precede it with a double unary:

=--AND(OR(A1=B1:B6),OR(A2=B1:B6))

It seems to me the logical translation would be to replace the cell references
I used with the one's you want. I don't understand why you added the 1 and 0
to the AND statement.

=--AND(OR(G59=K59:N59),OR(H59=K59:N59))

***ENTERED AS AN ARRAY FORMULA***

SEE MY POST FOR INSTRUCTIONS HOW TO DO THIS


--ron