Match data

[Nadine]

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.

 

[Eva]

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]

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

 

[T. Valko]

I'm using Excel 2003.

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.

 

[Ashish Mathur]

Hi,

Try this

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

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com