Menu
You can use the MATCH() function to check if the values in column A also exist in column B. MATCH() returns the position of a cell in a row or column. The syntax for MATCH() is =MATCH(lookup_value, lookup_array, [match_type]) . Using MATCH, you can look up a value both horizontally and vertically.
Use MATCH to determine whether each row in column A appears in column B, then filter column A to only the rows for which MATCH returned #N/A (i.e., that row's value in column A could not be found in column B):
=FILTER(A:A, ISNA(MATCH(A:A, B:B, 0)))
If A contains duplicates and you want to reduce the result sequence to unique values, just wrap the whole thing in UNIQUE:
=UNIQUE(FILTER(A:A, ISNA(MATCH(A:A, B:B, 0))))
Based on Kate aswer I have been able to negate not only one column, but several.
Kate solution was as follows:
=FILTER(A:A, ISNA(MATCH(A:A, B:B, 0)))
Where "B:B" is defining that what is going to be returned is A:A less B:B.
But if you want to return A:A, lees B:B, less C:C, less D:D, etc? Just insert B:B, C:C and D:D inside {}, then:
=FILTER(A:A; ISNA(MATCH(A:A; {B:B;C:C;D:D}; 0)))
I hope this may help others like me. I was seeking for a solution that would not bring what's present in some set of columns.
Thanks!
In column C, use vlookup from A against B. For example
C1 = vlookup(A1,B:B,1)
C2 = vlookup(A2,B:B,1)
...
If B does not contain A, then it shows #N/A
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With