You can use the following formula in Excel to compare two columns and find missing values:
=FILTER(A2:A13, ISNA(VLOOKUP(A2:A13, B2:B7, 1, FALSE)))
This particular formula finds all of the values in A2:A13 that are missing from the range B2:B13.
The following example shows how to use this formula in practice.
Example: Compare Two Columns for Missing Values in Excel
Suppose we have the following two lists of names in Excel:
Suppose we would like to find all of the names from List A that are missing in List B.
We can type the following formula into cell D2 to do so:
=FILTER(A2:A13, ISNA(VLOOKUP(A2:A13, B2:B7, 1, FALSE)))
The following screenshot shows how to use this formula in practice:
The formula returns every name from List A that is missing from List B.
For example:
- The name “Bob” appears in List A but it does not appear in List B.
- The name “Chad” appears in List A but it does not appear in List B.
- The name “Doug” appears in List A but it does not appear in List B.
And so on.
How This Formula Works
Recall the formula that we used to find values in A2:A13 that were missing from B2:B7:
=FILTER(A2:A13, ISNA(VLOOKUP(A2:A13, B2:B7, 1, FALSE)))
Here is how this formula works:
First, we use VLOOKUP to attempt to look up each value from A2:A13 in B2:B7.
If the name is found, then the name is returned. Otherwise, #N/A is returned.
Then, we use FILTER along with the ISNA function to filter the names from A2:A13 that had a result of #N/A when we used the VLOOKUP function.
The end result is that we’re able to only see the names from A2:A13 that are missing from B2:B7.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
How to Compare Two Lists in Excel Using VLOOKUP
How to Compare Two Excel Sheets for Differences
How to Compare Dates Without Time in Excel
