CODEWITHSUNDEEP
excelexcel-formula
MochaLatte
MochaLatte

Reputation: 23

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

I have a main list that I am using: Main List

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: Do not add list

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:

New list

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.

Upvotes: 1

Views: 3656

Answers (3)

JvdV
JvdV

Reputation: 75920

Try:

enter image description here

Formula in C2:

=FILTER(A2:A8,COUNTIF(B2:B6,A2:A8)=0)

Upvotes: 4

Harun24hr
Harun24hr

Reputation: 36945

You can use FILTER() formula like-

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

enter image description here

Upvotes: 1

Lev Gelman
Lev Gelman

Reputation: 205

I you can use VLOOKUP to check if it exists: enter image description here

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

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

Upvotes: 1

Related Questions