Using an "Unsubscribe" list

[Fitch]

I have a column in sheet 1 that has all the members emails in a column (J2:J200).
Some members don’t want the newsletter, so I wanted to create a list in another “Unsubscribe” sheet (say B5:B25) with those who wish to opt out.

I really just need the relevant addresses in Sheet 1, column M to be blanked if there is a match in the “Unsubscribe” list.

I tried VLOOKUP, but keep getting #NA every now and then.

=IF(VLOOKUP(J2,Unsubscribe.B$5:B$25,1)=J2,"",J2)

I an totally confused with ISNA, and have been trying to understand it for hours. I just can’t get my head around it.

Can anyone tell me how to do this?

Thanks.

O.K.
After several hours, I came up with:

=IF(ISNA(VLOOKUP(J2,Unsubscribe.B$5:B$50,1)=J2),J2,IF(VLOOKUP(J2,Unsubscribe.B$5:B$50,1)=J2,"",J2))

Really?
There’s got to be a less messy way…

Fitch

[Lupp]

Please note: The comma as the argument delimiter in formulae does not work in locales using the comma as the decimal separator (majority of locales!). The semicolon in this place works everywhere.
It was a bad decision, imo, to “localise” LibO with this respect. You can revert to the former state, which is better suited to support international cooperation, as described in this thread.

[Lupp]

Your task is basically one requiring database means like shown by the pseudo-SQL
SELECT EMAIL FROM SUBSCRIBERS WHERE EMAIL NOT IN (SELECT EMAIL FROM UNSUBSCRIBERS)

How to solve tasks of the kind is often asked, but there is no definitre answer as Calc isn’t a database. Depending on specifics of a question many solutions and more variants are possible.

A Create a column selecting the valid rows by a formula (Boolean result e.g.) 
  and use the AutoFilter. Then Copy/Paste.  
B Create a column selecting the valid rows by a formula (Boolean result e.g.) 
  and use the StandardFilter with the option 'Copy results to:'.  
C Tinker a formula to get get the results by suppressing the invalid rows. AutoFilter or ...  
D Use helper column (See A, B) and tinker formulas compacting the result without 
  using filters requiring interaction for updating.  
E Write a a custom function for the helping.  Then ...  
F Write a highly specialised "macro" ...  
...  
Z ...

Your approach (as I understand it) was C (without the filters). To use VLOOKUP just for a test is kind of a misuse, however. Use MATCJH for the purposeto get it clearer and simpler.

A solution along the D way is also included in this attached example. As I think a list of only 50 isn’t difficult to maintain by eye, brain, and keyboard, I prepared it for up to 2000 rows of data. The compacting part contains scaling formulae. In the other parts the ranges are given as constants. You may enhance the solution with this respect.