Search Range for item in seperate range

[rrstudio2]

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

 

[Mbt6]

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.

 

[Dave Peterson]

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

 

[rrstudio2]

Thanks, that seems to work well, although I can't say I understand what
it is doing. I am not familier with the -- notation, what is that for?
ALso, how is comparing a range against an array different than
comparing a range against a range? I don't see why it works if you
convert it to an array.
-Andrew V. Romero

 

[rrstudio2]

Wouldn't this require that the two list are exactly the same? In the
above example, I only want to know if A1 thru c1 contains any of the
items in list 2, so does A1 to c1 contain pink, or blue or purple? If
yes, I want it to say found. I sort of found a work around, I can just
type in a lot of OR statements and do
=if( or(a1=$L1:$o$1,b1=$L1:$o$1,c1=$L1:$o$1),"Found","Not Found"), but
in real life I would have to put in about 8 OR clauses, so it seems
like there should be a shortcut.

Thanks,
Andrew V. Romero

 

[rrstudio2]

Thanks, this seems to work, however I can't say I understand the
formula. I haven't seem the -- before, what does that do? So it looks
like transpose is creating an array, how is checking the array
different than simply checking a range such as L1:O1?

-Andrew

 

[Dave Peterson]

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

the -- converts true/false to 1/0.

If you select (in the formula bar) A1:C1=TRANSPOSE(L1:O1)
and hit the F9 button, you'll see how the helps do each comparison.