How can I use Google Sheets to compare two columns and identify missing values?

---

You can use the following formula in Google Sheets 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:B7.

The following example shows how to use this formula in practice.

Example: Compare Two Columns for Missing Values in Google Sheets

Suppose we have the following two lists of names in Google Sheets:

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

=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 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 Google Sheets: