Find Missing Values – Excel & Google Sheets
Download the example workbook
This tutorial will demonstrate how to check whether items are missing within a list in Excel and Google Sheets.
Find Missing Values
There are several ways to check if items are missing from a list.
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.
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.
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.
Start by performing an exact-match VLOOKUP (FALSE) for the values in your list.
If the item is found, the VLOOKUP Function will return that item, otherwise it will return the #N/A error.
You can use the ISNA Function to convert the #N/A errors to TRUE, indicating that those values are missing.
All non-error values result in FALSE.
Then convert the results of the ISNA Function to show whether the value is missing. If the VLOOKUP Function 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.