Match data
 

I have some data I need to have returned if it matches something else. The
data pictured below is in worksheet 2. In cell A2 of worksheet 1 is 153.
The returned value would be No because below is a Yes and a No. If there is
a No anywhere in this list, the result should be No. The result for 156
would be Yes because that is the only option in the data below. The result
for 203 would be No because it is listed regardless of whether or not there
is a Yes listed for that one.


153 Yes
156 Yes
153 No
153 No
201 No
156 Yes
203 No
203 Yes

I need the "No" as the result if it appears anywhere in the list to the item
I need to match. If there isn't a "No" in the list to the matching item,
then the result would be "Yes". I hope this makes sense. I'm using Excel
2003. Thank you.

Match data
 

paste the formula
=IF(SUM(IF($A$2:$A$20=A2,IF($B$2:$B$20="No",1,0),0 ))=0,"Yes","no")
Please note that is the array formula (please click : ctrl+shift+enter)
--
Please click "yes" if this post helped you!

Greatly appreciated

Eva


"Nadine" wrote:

I have some data I need to have returned if it matches something else. The
data pictured below is in worksheet 2. In cell A2 of worksheet 1 is 153.
The returned value would be No because below is a Yes and a No. If there is
a No anywhere in this list, the result should be No. The result for 156
would be Yes because that is the only option in the data below. The result
for 203 would be No because it is listed regardless of whether or not there
is a Yes listed for that one.


153 Yes
156 Yes
153 No
153 No
201 No
156 Yes
203 No
203 Yes

I need the "No" as the result if it appears anywhere in the list to the item
I need to match. If there isn't a "No" in the list to the matching item,
then the result would be "Yes". I hope this makes sense. I'm using Excel
2003. Thank you.

Match data
 

Can I use whole columns for $A$2:$A$20 and for $B$2:$B$20?

"Eva" wrote:

paste the formula
=IF(SUM(IF($A$2:$A$20=A2,IF($B$2:$B$20="No",1,0),0 ))=0,"Yes","no")
Please note that is the array formula (please click : ctrl+shift+enter)
--
Please click "yes" if this post helped you!

Greatly appreciated

Eva


"Nadine" wrote:

I have some data I need to have returned if it matches something else. The
data pictured below is in worksheet 2. In cell A2 of worksheet 1 is 153.
The returned value would be No because below is a Yes and a No. If there is
a No anywhere in this list, the result should be No. The result for 156
would be Yes because that is the only option in the data below. The result
for 203 would be No because it is listed regardless of whether or not there
is a Yes listed for that one.


153 Yes
156 Yes
153 No
153 No
201 No
156 Yes
203 No
203 Yes

I need the "No" as the result if it appears anywhere in the list to the item
I need to match. If there isn't a "No" in the list to the matching item,
then the result would be "Yes". I hope this makes sense. I'm using Excel
2003. Thank you.

Match data
 

I'm using Excel 2003.
Please note that is the array formula
Can I use whole columns for $A$2:$A$20 and for $B$2:$B$20?

No, you can't use entire columns as range references with Excel 2003. You
can in Excel 2007.

--
Biff
Microsoft Excel MVP


"Nadine" wrote in message
...
Can I use whole columns for $A$2:$A$20 and for $B$2:$B$20?

"Eva" wrote:

paste the formula
=IF(SUM(IF($A$2:$A$20=A2,IF($B$2:$B$20="No",1,0),0 ))=0,"Yes","no")
Please note that is the array formula (please click : ctrl+shift+enter)
--
Please click "yes" if this post helped you!

Greatly appreciated

Eva


"Nadine" wrote:

I have some data I need to have returned if it matches something else.
The
data pictured below is in worksheet 2. In cell A2 of worksheet 1 is
153.
The returned value would be No because below is a Yes and a No. If
there is
a No anywhere in this list, the result should be No. The result for
156
would be Yes because that is the only option in the data below. The
result
for 203 would be No because it is listed regardless of whether or not
there
is a Yes listed for that one.


153 Yes
156 Yes
153 No
153 No
201 No
156 Yes
203 No
203 Yes

I need the "No" as the result if it appears anywhere in the list to the
item
I need to match. If there isn't a "No" in the list to the matching
item,
then the result would be "Yes". I hope this makes sense. I'm using
Excel
2003. Thank you.

Match data
 

Hi,

Try this

=if(sumproduct((sheet2!$A$2:$A$9=sheet1!$A2)*(shee t2!$A$2:$A$9="No"))=1,"No","Yes")

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Nadine" wrote in message
...
I have some data I need to have returned if it matches something else.
The
data pictured below is in worksheet 2. In cell A2 of worksheet 1 is 153.
The returned value would be No because below is a Yes and a No. If there
is
a No anywhere in this list, the result should be No. The result for 156
would be Yes because that is the only option in the data below. The
result
for 203 would be No because it is listed regardless of whether or not
there
is a Yes listed for that one.


153 Yes
156 Yes
153 No
153 No
201 No
156 Yes
203 No
203 Yes

I need the "No" as the result if it appears anywhere in the list to the
item
I need to match. If there isn't a "No" in the list to the matching item,
then the result would be "Yes". I hope this makes sense. I'm using Excel
2003. Thank you.