I'm quite novice so I apologize if this is obvious. I'm trying to have an import range show names and another value from another google sheet, but exclude those from a blacklist. I have pasted the formula I'm currently working with below. Any ideas?

=QUERY(IMPORTRANGE("XXXXX", "XXXX!$A:$B"), " skipping != 'Blacklist'!$A:$A")

The import range it pulls from is a Filter already, I've pasted that below. Could it be worked there?

=IFERROR(FILTER('XXXXX'!$A:$B, 'XXXXXX'!$H:$H = "ABCD")," ")

Thanks in advance!

CodePudding user response：

FILTER is my preferred way of removing from a list.

Here is the formula

=FILTER(A2:A6,NOT(COUNTIF(B2:B3,A2:A6)))

You can think of it like

=FILTER( list ,NOT(COUNTIF( needle , haystack)))

Usually you use this to whitelist a document (hence why I dedicated this formula to memory in a needle-haystack fashion.) But by throwing in the NOT in the criteria of the filter, it works great for blacklisting too!

As to why it works, I'm not sure, my best guess is when google sheets is running through the COUNTIF for any given cell if it meets the criteria it will return a '1' which is truthy and it includes it in the results. This also allows us to compare un-even amounts of data. ie. B2:B3 rather than B2:B6