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**

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 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:

How to Compare Strings in Google Sheets

How to Compare Three Columns in Google Sheets

How to Compare Dates in Google Sheets

Awesome!

converted to a named function. will be useful.