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 Main Function

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.

find missing values 01

Let’s see how this formula works.

AutoMacro - VBA Code Generator

COUNTIF Function

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

find missing values 02

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 01

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.

find missing values 04

Let’s go through this formula.

VLOOKUP Function

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

find missing values 05

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.

find missing values 06

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”.

find missing values 07

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.

find missing values Google Function

Excel Practice Worksheet

Practice Excel functions and formulas with our 100% free practice worksheets!

  • Automatically Graded Exercises
  • Learn Excel, Inside Excel!