I did enter it as an array formula, the braces do not copy and paste but they
are in there. The 1,0 was just added after the first formula did not return
anything but value or false when the first two numbers of the four numbers
did exist in the six number set.

"Ron Rosenfeld" wrote:

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