wrote in message
oups.com...
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



Perhaps not the best way

1) You could create a cell in D1 that contains all of the data from A1
through C1...something like
2) =a1&"|"&b1&"|"&C1 (I put the &"|" to show as a separator, the
concatinate formula works similar to this)
3) Now do the same for L1-O1 in say column P(in this case l - o is 4 columns
so it may not work, but in your example above your second list pink blue
purple only had 3 items
4) Then do a Vlookup from the data in D and compare against P, and copy
down. If there is no match, the vlookup will return an "#n/a" error.