How about:

=IF(SUM(--(A1:C1=TRANSPOSE(L1:O1)))0,"found one","nope")

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

" wrote:

Lets say in colums a1-c1 I have
red blue orange

then i columns L1-o1 I have
pink blue purple

I want to see if an any of the items in the first list match any item
in the second list. I was trying an array formula like:

=if( or(A1:C1 = $L$1-$O$1),"found","not found")

The array formula seems to work if iI only search one range (i.e.
A1=$l$1:$O$1) but I can't seem to find the trick for getting it to use
both ranges. Any ideas?

-Andrew V. Romero

--

Dave Peterson