How to search table and return value

Question

I have a column of names and a column called is broke rules. If a person has broken a rule once, then their rule breaking status is yes otherwise they are not a rule breaker. I know how I would do this programmatically in python or something, but how do I check their actual rule breaking status in excel?

name	broke rule
bob	no
bob	no
jane	no
sam	yes
jane	yes
jake	no
bob	yes
paul	no

The result I want

name	broke rule	rule breaking status
bob	no	yes
bob	no	yes
jane	no	yes
sam	yes	yes
jane	yes	yes
jake	no	no
bob	yes	yes
paul	no	no
jake	no	no

As you can see only jake and paul have a no status for their rule breaking, because they have not broken the rule once, while the others have broken a rule at least once before.

Answer 1

If your Excel version is older than 2010 you can do:

=IF(SUMPRODUCT(--(A$2:A$10=A2),--(B$2:B$10="yes"))=1,"yes","no")

Answer 2

Use COUNTIFS:

=IF(COUNTIFS(A:A,A2,B:B,"yes"),"yes","no")