And don't change Ron's formula.

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!

"Ron Rosenfeld" wrote:

On Sat, 14 Oct 2006 11:41:01 -0700, Shu of AZ <Shu of
wrote:

I have four cells with numbers in each, (2)(4)(9)(1), then I have 6 adjacent
cells with numbers in each of them, (4)(3)(5)(8)(9)(2).
In a different cell, I need to represent a true statement that indicates if
the first two numbers in the cell's content, (2) or (4), are contained in any
of the 6 cell's content,(4)(3)(5)(8)(9)(2). If there is a match, in the one
cell that has to represent a hit, it will place a 1 in the cell. Then with a
conditional formula I can turn the cell red.

=if(or(a1=k1,a1=k2,a1=k3,a1=k4,a1=k5,a1=k6, ???(AND(a2=k1 and so on, , ,

How do you combine the OR and AND statement in one formula or is there
another way to do this.

Assuming the 4 numbers are in A1:A4, and the 6 numbers are in B1:B6, it sounds
as if you want to return a 1 if A1 is found in B1:B6 and also A2 is found in
B1:B6.

There are many ways of doing that.

One way is with an **array** formula:

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

To enter an **array** formula, after typing in the formula, hold down
<ctrl<shift while hitting <enter. XL will place braces {...} around the
formula.

This will return either TRUE, if both match, or FALSE if only one or none
matches.

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))


--ron


--

Dave Peterson