Values in a column which are NOT IN another column

Utsav

Hey everyone! 👋 Check out this handy formula for filtering values from one column that aren’t present in another column.

Here’s the formula:

=UNIQUE(FILTER(Submissions!A2:A, ISNA(MATCH(Submissions!A2:A, Status!A2:A, 0))))

This formula works by filtering data in the Submissions column that doesn’t appear in the Status column. The UNIQUE function ensures that only unique values are returned.

If you’re dealing with spreadsheet data and need to identify differences between two columns, give this formula a try!

Feel free to discuss or share your own approaches below. I am also sharing the 📊💬 Original Stack Overflow post

Karoline from Sheetgo

Utsav Hi Utsav,

Thanks for the tip. Sharing a visual example would be quite helpful for us to better understand the topic at hand. Could you kindly provide us with an illustrated example?

Thanks 🙃

Utsav

Karoline from Sheetgo

sure

lets say we have values in 2 columns as shown below

we want to find out values in column A which are not in column B

so the result would be

Eugenia

I do this in a slightly different way. Particularly cause I tend to avoid the Match formula. I find it’s kinda heavy on the spreadsheet processing.

=IFERROR(FILTER(A2:A,NOT(COUNTIF(B2:B,A2:A))))

And I always pack everything inside IFERRORs to prevent N/A’s when no match is found.😉