Finding a number(s)

[louis welna]

Thank you so much for the possible solutions. I needed to be more specific.

I'm working with two sets of three numbers which reflect the standard error of the measurement of two obtained scores. The rule is: if the scores touch in any way, then there is no significant difference between the two score and should not be interpreted!
TEST 1
In this example there is a lot of touching going on!<pre> C D E
Low Mid High
7 97 103 108
8 93 98 102</pre>

So, I'm looking for a formula that checks to see if any one number located in c8:e8 (that would include 93, 94,95,96,97,98,99,199,101,102) is found anywhere in c7:e7 (that would include these numbers (97,98,99,100,101,102,103,104,105,106,107,108). I know the answer is "Yes" for there are 6 matches.

TEST 2<pre> C D E
Low Mid High
10 97 103 108
11 91 94 97</pre>

There is only touching at the ends.The answer would also be "yes".

TEST 3<pre> C D E
Low Mid High
13 93 98 103
14 76 94 97</pre>

No touching at all! The answer would be "no".

I'm sorry I am so wordy. Thank you all for your help.

Louie Welna
This message was edited by Juan Pablo G. on 2002-10-16 14:13

 

[Juan Pablo González]

Why, in Test3 there are no matches ? shouldn't the "intersection" be 93,94,95,96,97 ?

 

[Aladin Akyurek]

Is this perhaps what you're looking for:

=SUMPRODUCT((ISNUMBER(MATCH(ROW(INDIRECT(C8&":"&E8)),ROW(INDIRECT(C7&":"&E7)),0))+0))

This creates a set of whole numbers from Low to High before matching.

 

[Mark W.]

Or...

{=OR(ROW(INDIRECT(C8&":"&E8))=TRANSPOSE(ROW(INDIRECT(C7&":"&E7))))}

Note: This is an array formula which must be entered using the Control+Shift+Enter key combination. The outermost braces, { }, are not entered by you -- they're supplied by Excel in recognition of a properly entered array formula. For more on array formulas see the Excel Help topic for "About array formulas and how to enter them".

 

[Juan Pablo González]

Hey, I had one of my own also:

{=OR(ISNUMBER(MATCH(ROW(INDIRECT(C8&":"&E8)),ROW(INDIRECT(C7&":"&E7)),0)))}

Array entered as well.

 

[louis welna]

On 2002-10-16 15:36, Juan Pablo G. wrote:
Hey, I had one of my own also:

{=OR(ISNUMBER(MATCH(ROW(INDIRECT(C8&":"&E8)),ROW(INDIRECT(C7&":"&E7)),0)))}

Array entered as well.