My filter function is almost there, but not quite. I want to display only those rows in List1 one that are not in List2, based on the email column (c). This works without the NOT and it displays only the matches, so of course I need to add a NOT to display only those that do not match.

Here is my almost working filter thus far. I am getting an error with this though, from adding the NOT. How do I fix it?

=filter(List1!A2:D199, NOT(match(List1!C2:C199,List2!C1:C11, 0)))

1 Answer 1


Oh, lol, I found the answer two minutes later. I love when that happens. I need to use ISNA instead of NOT. (Or at least, ISNA seems to work fine to get the output I need). So the filter with match becomes the following:

=filter(List1!A2:D199, ISNA(match(List1!C2:C199,List2!C1:C11, 0)))

This works. Yay!

  • 1
    When you find and post your own answer, it helps the community at large to mark it as "Best Answer," just as you would with anyone else's post. It lets contributors know at a glance from the main listings page which items have already been resolved; and it helps future site visitors most quickly find relevant solutions.
    – Erik Tyler
    Jul 8, 2021 at 16:18
  • @ErikTyler :) the system won't let me for another two days :D Jul 8, 2021 at 17:38
  • 1
    Wow, that's a new one one me (and a bit odd, it seems to me). Thanks for sharing. I'll tuck that information away.
    – Erik Tyler
    Jul 8, 2021 at 18:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.