Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search Range for item in seperate range
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search Range for item in seperate range
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search Range for item in seperate range
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search Range for item in seperate range
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search Range for item in seperate range
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","N ot 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search Range for item in seperate range
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search Range for item in seperate range
=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. " wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
array range in seperate sheets | Excel Worksheet Functions | |||
Search range for text not in another range | Excel Discussion (Misc queries) | |||
How to search a range for a suburb in a large range | Excel Programming | |||
copy range from each worksheet to seperate workbook | Excel Programming | |||
copy range from each worksheet to seperate workbook | Excel Programming |