I have an outdated table of registered members and a second up to date table (which is actually only a list with member numbers). I need to check the old table for members who left and put these (who left) to a new table.

So basically:

• if B2 != any value in column A(on Sheet2) copy row 2 (from Sheet1) to A2(Sheet3);
• if B3 != any value in column A(on Sheet2) copy row 3 (from Sheet1) to the next free row on A(Sheet3);

Alternatively, deleting rows on the first table and skipping the creation of Sheet3 is possible as well.

I mustn't use scripts, so I hope there is a function only solution possible.

If it helps, Sheet2 is variable, I can put the values from column A to Column B (or any other).

I tried =search, =if, =not, =vlookup and even failed more horribly on =query.

CodePudding user response：

if B2 != any value in column A(on Sheet2) copy row 2 (from Sheet1) to A2(Sheet3)

Use filter(), like this:

=filter(Sheet1!A2:C, isna(match(Sheet1!B2:B, Sheet2!A2:A, 0)))