|
match cell in array1 with all cells in array2
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 |
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 |
....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 |
| All times are GMT +1. The time now is 02:35 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com