How to create a new list that excludes a list from the main list used-CodePudding

I have a main list that I am using:

I am currently trying to filter out the main list by ensuring that the main list does not contain any entry from the "Do not add list" as shown in this image:

My attempt to solve this issue was to create a new list by using an If statement and name range:

=IF(List1=List2,"",List1) . (The name range of List1 is referring to the Main list and the List2 name range is referring to the "Do not add list".

As a result I had the following output:

The new list contains ABCD still even though it is not supposed to be since it is on the do not add list. Any help on being able to create a filtered main list is appreciated.

CodePudding user response：

I you can use VLOOKUP to check if it exists:

The Do not add list defined as List2 ,The formula is:

=IF(ISNA(VLOOKUP(A3,List2,1,0)),A3,"")

CodePudding user response：

You can use FILTER() formula like-

=FILTER(A2:A17,NOT(ISNUMBER(XMATCH(A2:A17,$D$4:$D$17,0))))