Find Missing Values – Excel & Google Sheets

This tutorial will demonstrate how to check whether or not an element of a list exists in another list in Excel and Google Sheets.

Find Missing Values

You may want to identify values in one list that are missing in another list. There are several ways to do this, and we will step through a few of them below.

Find Missing Values with COUNTIF

One way to find missing values in a list is to use the COUNTIF Function together with the IF Function.

Let’s see how this formula works.

COUNTIF Function

The COUNTIF Function counts the number of cells that meet a given criterion. If no cells meet the condition, it returns zero.

In this example, “#1103” and “#7682” are in Column B, so the formula gives us 1 for each. “#5555” is not in the list, so the formula gives us 0.

IF Function

The IF Function will evaluate any non-zero number as TRUE, and zero as FALSE.

Within the IF Function, we perform our count, then output “Yes” for TRUE and “No” for FALSE. This gives us our original formula of:

Find Missing Values with VLOOKUP

Another way to find missing values in a list is to use the VLOOKUP and ISNA Functions together with the IF Function.

Let’s go through this formula.

VLOOKUP Function

Start by performing an exact-match vlookup for the values in your list.

We use “FALSE” in the formula to require an exact match. If the element you are looking for is in your list, the VLOOKUP Function will return that element; if it is not there, it will return an #N/A error.

ISNA Function

You can use the ISNA Function to convert the #N/A errors to TRUE, meaning that those elements are missing.

All non-error values result in FALSE.

IF Function

Then convert the results of the ISNA Function to show whether the value is missing. If the vlookup gave us an error, the item is “Missing”.

Item in both lists display “Yes”.

Combining these steps gives us the original formula:

Find Missing Values in Google Sheets

These formulas work exactly the same in Google Sheets as in Excel.