Skip to content

How to List Missing Values in Google Sheets

Let’s say you have two lists. They are similar but not the same.

Your goal is to see which values from list one are missing from list two. Here is how you do it.

The Formula

Select the top cell where you want your results to be.

Enter the following formula.

=FILTER(List1, NOT(COUNTIF(List2, List1)))

Google Sheets lists the values from list one that do not appear in list two.

Formula Explanation

Let’s look at how this formula works. We will work from the inside out and begin with the COUNTIF function.

=COUNTIF(List2, List1)

The COUNTIF function references list two as the range and list one as the criteria. In other words, the function checks how many times each value in list one appears in the range of list two.

The formula results in an array of numbers. This array of numbers passes to the NOT function.

=NOT({1, 1, 0, 0, 1, 0, 1})

The NOT function turns any number into the value of FALSE and any zero into the value of TRUE.

={FALSE, FALSE, TRUE, TRUE, FALSE, TRUE, FALSE}

The array of TRUE false values then passes to the FILTER function, which returns only those colors that align with a TRUE value.

=FILTER(List1, {FALSE, FALSE, TRUE, TRUE, FALSE, TRUE, FALSE})

As a result, you get back the values missing from list two.

This Post Has 0 Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Back To Top