Hi everyone,

I have a range of ID data in column B. Column D is where I can give my input. I want column F to print the ID in column B whenever the ID is not appear in the list of column D. So based on the example in the screenshot above, column F will only have 21 output ID by excluding ID 1007,1001,1019,1021,1018.

I'm trying to use =FILTER($B$4:$B$29,B4:B29<>D4:D29) and =QUERY(B4:D29,"Select * where B is not D",0), seems like both are not working well. Any help will be greatly appreciated!

CodePudding user response：

Try

=FILTER(B4:B, countif(D4:D8,B4:B) = 0)