To compare 2 columns in Excel to find differences, you can proceed in this way:
Write the core formula to search for the first value from List 1 (A2) in List 2 ($C$2:$C$9):
VLOOKUP(A2, $C$2:$C$9, 1, FALSE)
Nest the above formula in the ISNA function to check the VLOOKUP's output for #N/A errors. In case of an error, ISNA yields TRUE, otherwise FALSE:
ISNA(VLOOKUP(A2, $C$2:$C$9, 1, FALSE))
Use the ISNA VLOOKUP formula for the logical test of the IF function. If the test evaluates to TRUE (#N/A error), return a value from List 1 in the same row. If the test evaluates to FALSE (a match in List 2 is found), return an empty string.
The complete formula takes this form:
=IF(ISNA(VLOOKUP(A2, $C$2:$C$9, 1, FALSE)), A2, "")
To get rid of the blanks, apply Excel's Filter as demonstrated in the above example.
Compare two columns in Excel to find missing values
