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.

basic · Accepted Answer · 2021-03-20 01:28:24Z

1

Use COUNTIFS:

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

answered Mar 20, 2021 at 1:28

basic

12k2 gold badges15 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

VBasic2008 · Accepted Answer · 2021-03-20 04:01:56Z

0

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")

answered Mar 20, 2021 at 4:01

VBasic2008

56.9k5 gold badges21 silver badges38 bronze badges

Collectives™ on Stack Overflow

How to search table and return value

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related