# Excel: How to Compare Two Columns for Missing Values

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:B7**.

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:

![](https://www.statology.org/wp-content/uploads/2023/06/miss1.png)

<div class="mv-ad-box" data-slotid="content_btf" id="bkmrk--1"><div class="mv-rail-frame-250" data-slotid="content_btf"><div class="mv-rail-slide-250" data-slotid="content_btf"><div class="mv-rail-sticky-250" data-slotid="content_btf"><div class="adunitwrapper content_btf_wrapper" data-nosnippet="" data-wrapper="content_btf" id="bkmrk--2"><div class="content_btf adunit" id="bkmrk--3"></div></div></div></div></div></div>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:

```
<strong>=FILTER(A2:A13, ISNA(VLOOKUP(A2:A13, B2:B7, 1, FALSE)))</strong>
```

<div class="mv-ad-box" data-slotid="content_2_btf" id="bkmrk--4"><div class="mv-rail-frame-250" data-slotid="content_2_btf"><div class="mv-rail-slide-250" data-slotid="content_2_btf"><div class="mv-rail-sticky-250" data-slotid="content_2_btf"><div class="adunitwrapper content_btf_wrapper" data-nosnippet="" data-wrapper="content_2_btf" id="bkmrk--5"><div class="content_btf adunit" id="bkmrk--6"></div></div></div></div></div></div>The following screenshot shows how to use this formula in practice:

![Excel compare two columns for missing values](https://www.statology.org/wp-content/uploads/2023/06/miss2.png)

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.

<div class="mv-ad-box" data-slotid="content_3_btf" id="bkmrk--8"><div class="mv-rail-frame-250" data-slotid="content_3_btf"><div class="mv-rail-slide-250" data-slotid="content_3_btf"><div class="mv-rail-sticky-250" data-slotid="content_3_btf"><div class="adunitwrapper content_btf_wrapper" data-nosnippet="" data-wrapper="content_3_btf" id="bkmrk--9"><div class="content_btf adunit" id="bkmrk--10"></div></div></div></div></div></div>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**:

```
<strong>=FILTER(A2:A13, ISNA(VLOOKUP(A2:A13, B2:B7, 1, FALSE)))</strong>
```

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.

<div class="mv-ad-box" data-slotid="content_4_btf" id="bkmrk--11"><div class="mv-rail-frame-250" data-slotid="content_4_btf"><div class="mv-rail-slide-250" data-slotid="content_4_btf"><div class="mv-rail-sticky-250" data-slotid="content_4_btf"><div class="adunitwrapper content_btf_wrapper" data-nosnippet="" data-wrapper="content_4_btf" id="bkmrk--12"><div class="content_btf adunit" id="bkmrk--13"></div></div></div></div></div></div>The end result is that we’re able to only see the names from **A2:A13** that are missing from **B2:B7**.