I wanted to get the value in column A which does not have a certain value on column B. For example, in this picture, given the set in column B when grouped using the value in column A, how can I get the values which does not have "4" in each set?
So the answer that I’m looking for is 2000 and 3000 because they do not have "4" in their respective sets in column B.
Is this achievable? I can’t wrap around my head if INDEX and MATCH can do this.
>Solution :
OK well I see that @JvdV reached the same answer as me which is always reassuring. I added a test for blank cells so:
=UNIQUE(FILTER(A1:A20,(COUNTIFS(A1:A20,A1:A20,B1:B20,4)=0)*(A1:A20<>"")))
You could invert the logic if you wanted to but it’s a bit longer:
=UNIQUE(FILTER(A1:A20,(COUNTIFS(A1:A20,A1:A20,B1:B20,"<>"&4)=COUNTIF(A1:A20,A1:A20))*(A1:A20<>"")))