Identifying "mismatch duplicates" with a Google Sheets formula-CodePudding

Not sure how to describe this one, so apologies for the vague title.

I'm trying to identify when a specific value in a column in Google Sheets appears more than once, but only if the value in a separate column is different. A visual will probably help here:

So in this scenario you can see that the 1111 ID is assigned to James twice and Nicole once. It's absolutely fine that 1111 is listed multiple times. But it's not good that it's assigned to more than one unique person. So my desire is for every row using the 1111 ID gets flagged with a formula (as seen in the 'Status' column) so that I can filter for it and handle the problem.

The example above uses names for the Owner, but that could be numbers instead.

Here is an example sheet:

CodePudding user response：

try:

=INDEX(IF(A2:A="",,IF(COUNTIFS(A2:A&B2:B, A2:A&B2:B)=1, "mismatch", "good")))