match cell in array1 with all cells in array2

L

Luke

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
 
K

KL

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)*Sheet3!G1)

Regards,
KL
 
A

Alan Beban

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
 
L

Luke

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

KL

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

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

KL
 
K

KL

stupid me! Here is a simple and fast one:

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

Regards,
KL
 
L

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
 
Top