How to exclude values from one table that exist in another using Google Sheets Query

[MarkSeeker91]

I have two separate tables in my Google Sheets named orders and processed. My goal is to identify all entries from the orders table that do not appear in the processed table.

I attempted to use the following query formula, but it isn’t functioning correctly:

=QUERY(orders!A:A,processed!A:A,"select orders!A:A WHERE A NOT IN processed!A:A")

I’m aiming to generate a list of order IDs that remain unprocessed. Unfortunately, I’m encountering an error with this syntax, and I’m confused about how to correctly structure the NOT IN condition when referencing data from two different sheets. Can anyone help me with the right way to write this query function to exclude matching values between the two tables?

[SwiftCoder42]

Your problem is that QUERY can’t reference external ranges in the WHERE clause. Google Sheets just doesn’t let you compare against ranges from different sheets that way. I’ve had good luck with ARRAYFORMULA and conditional logic instead. Try this: =ARRAYFORMULA(IF(COUNTIF(processed!A:A,orders!A:A)=0,orders!A:A,"")). This checks each order ID against your processed list. If there’s no match (count = 0), it shows the order ID. If there’s a match, it shows nothing. You’ll get all unprocessed orders in one column without the headache of nested functions. Works pretty well even with big datasets, but if you’re dealing with thousands of rows, use specific ranges instead of entire columns.

[mikezhang]

Your QUERY syntax won’t work because it can’t directly reference multiple ranges like that. You’d need something like =QUERY({orders!A:A;processed!A:A}, "select Col1 where Col1 is not null group by Col1 having count(Col1) = 1") but that gets messy fast. I’ve found it’s better to combine QUERY with other functions for this. Try =QUERY(orders!A:A, "select A where A is not null and A != ''") then filter out the processed ones separately - way more reliable. QUERY’s NOT IN clause has issues when working across different sheets, which is probably causing your error.

[Zack_45Gaming]

hey, that query isn’t gonna work like that. just use =FILTER(orders!A:A,NOT(ISNUMBER(MATCH(orders!A:A,processed!A:A,0)))) instead. its way simpler and it actually does the job!