Sheet1 has Array1 = g1:bw10
Sheet2 has Array2 = ca12:ct20

in sheet3 I need to display only the numbers from the range in sheet2 that
are equal to those in the range in sheet1.
I tried:
=IF(ISNA(MATCH(Sheet3!G1,Sheet3!$ca$12:$ct$20,0))= FALSE,"",Sheet3!G1)
filled down and accross as needed
But doesn't display anything even though there are matches.
Can you help me?
Thank you
Luke

Hi,

The MATCH function doesn't work on bidimensional ranges. Try this
array-entered (Ctrl+Shift+Enter) formula instead:

=IF(OR(Sheet3!G1=Sheet3!$CA$12:$CT$20),Sheet3!G1," ")

or this non-array formula:

=SUMPRODUCT(OR(Sheet3!G1=Sheet3!$CA$12:$CT$20)*She et3!G1)

Regards,
KL


"Luke" wrote in message
...
Sheet1 has Array1 = g1:bw10
Sheet2 has Array2 = ca12:ct20

in sheet3 I need to display only the numbers from the range in sheet2 that
are equal to those in the range in sheet1.
I tried:
=IF(ISNA(MATCH(Sheet3!G1,Sheet3!$ca$12:$ct$20,0))= FALSE,"",Sheet3!G1)
filled down and accross as needed
But doesn't display anything even though there are matches.
Can you help me?
Thank you
Luke

....or this array-entered (if the values are numbers):

=OR(Sheet3!G1=Sheet3!$CA$12:$CT$20)*Sheet3!G1

KL


"KL" wrote in message
...
Hi,

The MATCH function doesn't work on bidimensional ranges. Try this
array-entered (Ctrl+Shift+Enter) formula instead:

=IF(OR(Sheet3!G1=Sheet3!$CA$12:$CT$20),Sheet3!G1," ")

or this non-array formula:

=SUMPRODUCT(OR(Sheet3!G1=Sheet3!$CA$12:$CT$20)*She et3!G1)

Regards,
KL


"Luke" wrote in message
...
Sheet1 has Array1 = g1:bw10
Sheet2 has Array2 = ca12:ct20

in sheet3 I need to display only the numbers from the range in sheet2
that
are equal to those in the range in sheet1.
I tried:
=IF(ISNA(MATCH(Sheet3!G1,Sheet3!$ca$12:$ct$20,0))= FALSE,"",Sheet3!G1)
filled down and accross as needed
But doesn't display anything even though there are matches.
Can you help me?
Thank you
Luke

stupid me! Here is a simple and fast one:

=IF(COUNTIF(Sheet3!$CA$12:$CT$20,Sheet3!G1),Sheet3 !G1,"")

Regards,
KL



"KL" wrote in message
...
...or this array-entered (if the values are numbers):

=OR(Sheet3!G1=Sheet3!$CA$12:$CT$20)*Sheet3!G1

KL


"KL" wrote in message
...
Hi,

The MATCH function doesn't work on bidimensional ranges. Try this
array-entered (Ctrl+Shift+Enter) formula instead:

=IF(OR(Sheet3!G1=Sheet3!$CA$12:$CT$20),Sheet3!G1," ")

or this non-array formula:

=SUMPRODUCT(OR(Sheet3!G1=Sheet3!$CA$12:$CT$20)*She et3!G1)

Regards,
KL


"Luke" wrote in message
...
Sheet1 has Array1 = g1:bw10
Sheet2 has Array2 = ca12:ct20

in sheet3 I need to display only the numbers from the range in sheet2
that
are equal to those in the range in sheet1.
I tried:
=IF(ISNA(MATCH(Sheet3!G1,Sheet3!$ca$12:$ct$20,0))= FALSE,"",Sheet3!G1)
filled down and accross as needed
But doesn't display anything even though there are matches.
Can you help me?
Thank you
Luke

KL
I tried the last formula you posted and I get random hits in various places
but they are not corresponding to the actual position(s) where they are
located at in sheet2.
I was thinking that the number from any given sell in sheet1 would show up
in sheet3 in the exact position of the found match in sheet2.
does that make since?
Luke

"KL" wrote:

stupid me! Here is a simple and fast one:

=IF(COUNTIF(Sheet3!$CA$12:$CT$20,Sheet3!G1),Sheet3 !G1,"")

Regards,
KL



"KL" wrote in message
...
...or this array-entered (if the values are numbers):

=OR(Sheet3!G1=Sheet3!$CA$12:$CT$20)*Sheet3!G1

KL


"KL" wrote in message
...
Hi,

The MATCH function doesn't work on bidimensional ranges. Try this
array-entered (Ctrl+Shift+Enter) formula instead:

=IF(OR(Sheet3!G1=Sheet3!$CA$12:$CT$20),Sheet3!G1," ")

or this non-array formula:

=SUMPRODUCT(OR(Sheet3!G1=Sheet3!$CA$12:$CT$20)*She et3!G1)

Regards,
KL


"Luke" wrote in message
...
Sheet1 has Array1 = g1:bw10
Sheet2 has Array2 = ca12:ct20

in sheet3 I need to display only the numbers from the range in sheet2
that
are equal to those in the range in sheet1.
I tried:
=IF(ISNA(MATCH(Sheet3!G1,Sheet3!$ca$12:$ct$20,0))= FALSE,"",Sheet3!G1)
filled down and accross as needed
But doesn't display anything even though there are matches.
Can you help me?
Thank you
Luke

Do you mean that if a number in any position in Array2 is included at
least once in any position(s) in Array1, you want it included in the
output list? Are the numbers in Array 2 unique? If not, do you want the
non-unique numbers included more than once in the output list if they
are also included in Array1?

Alan Beban

Luke wrote:
Sheet1 has Array1 = g1:bw10
Sheet2 has Array2 = ca12:ct20

in sheet3 I need to display only the numbers from the range in sheet2 that
are equal to those in the range in sheet1.
I tried:
=IF(ISNA(MATCH(Sheet3!G1,Sheet3!$ca$12:$ct$20,0))= FALSE,"",Sheet3!G1)
filled down and accross as needed
But doesn't display anything even though there are matches.
Can you help me?
Thank you
Luke

any number that it matches (regardless of duplicates) can show in the output
sheet 3. No unique numbers

"Alan Beban" wrote:

Do you mean that if a number in any position in Array2 is included at
least once in any position(s) in Array1, you want it included in the
output list? Are the numbers in Array 2 unique? If not, do you want the
non-unique numbers included more than once in the output list if they
are also included in Array1?

Alan Beban

Luke wrote:
Sheet1 has Array1 = g1:bw10
Sheet2 has Array2 = ca12:ct20

in sheet3 I need to display only the numbers from the range in sheet2 that
are equal to those in the range in sheet1.
I tried:
=IF(ISNA(MATCH(Sheet3!G1,Sheet3!$ca$12:$ct$20,0))= FALSE,"",Sheet3!G1)
filled down and accross as needed
But doesn't display anything even though there are matches.
Can you help me?
Thank you
Luke