How do I exclude a list of values using a filter in Google Sheets?

Question

In Google Sheets, I have a filter that does this:

=FILTER(A:K, B:B <> "username1")

That works to exclude all rows that have username1 in the B cell. But I want to exclude a list of usernames. So I tried:

=FILTER(A:K, (B:B <> "username1")+(B:B <> "unsername2"))

But that excluded nothing. I would have thought that filter should exclude all rows with either of those usernames.

Answer 1

The + operator corresponds with an or condition. Your test will yield true for any value, because a value is always different from either value1 or value2 or both, when value1 and value2 are not equal.

To make it work, use an and condition, like this:

=filter(A2:K, B2:B <> "username1", B2:B <> "username2")

Alternatively, put the usernames in range R2:R and use a filter(match()) pattern like this:

=filter(A2:K, isna(match(B2:B, R2:R, 0)))