Google Sheets - Find Values in One Column that are Missing in Another Column

G

How to compare columns in Google Sheets and highlight cell values that are in one column but missing in another column.

Published in: Google Sheets - Formulas and Functions

A small business maintains their staff roster in a simple Google Sheet - the column A of the sheet contains a list of all employee names and column B contains a list of employees who have been assigned to a project.

Items in column A that are not in column B

The immediate task is to identify staff members who are part of the organization but have not been assigned any project yet. In other words, the manager needs to figure out all employee names from column A who are not preset in column B.

There are two ways to solve this problem - visually and through formulas.

Using Visual Formatting

The first option would be to highlight cells in column A that are missing in column B.

Inside the Google Sheet, go to the Format menu and choose conditional formatting. Here select A2:A for the range field, choose Custom Formula from the Format Cells If dropdown and paste the formula:

=COUNTIF(B$2:B, A2)=0

The COUNTIF function will essentially count the occurrence of each item in Column A against the range of cells in Column B. If the count for an item in Column A is 0, it means that the cell value is not present in column B and the cell is highlighted with a different background color.

Visual Formatting - Missing Column Values

Find Missing Items in Another Column

The next approach uses Google Sheet formulas to create a list of items that are in Column A but missing in Column B.

We’ll make use of the FILTER function that, as the name suggests, returns only a filtered version of a range that meets a specific criteria. In our case, the criteria is similar to the one that we used in the visual formatting section.

Go to column C (or any blank column) and enter this formula in the first empty cell.

=FILTER(A2:A,ISNA(MATCH(A2:A,B2:B,0)))

Google Sheets MATCH function

The MATCH function returns the position of items in Column A in the range associated with Column B and it returns #N/A if the values is not found. When the result is used with ISNA, it returns true only when the match is not found.

Using Google Query Language

SQL geeks may also use the Google Query Language, we are used it with D3.js visualization, to print the list of names that are in Column B but not in Column B.

=QUERY(A2:A,
   "SELECT A WHERE A <> ''
    AND NOT A MATCHES '"&TEXTJOIN("|",TRUE,B2:B)&"'
    ORDER BY A")

missing values - Google Query

The matches operator in the where clause does a regex comparison and the order by clause in the query will automatically sort the output alphabetically.

📮  Subscribe to our Email Newsletter for Google tips and tutorials!
Published in: Google Sheets - Formulas and Functions

Looking for something? Find here!

Meet the Author

Web Geek, Google Developer Expert
A
Amit Agarwal

Amit Agarwal is a Google Developer Expert in Google Workspace and Google Apps Script. He holds an engineering degree in Computer Science (I.I.T.) and is the first professional blogger in India. He is the developer of Mail Merge for Gmail and Document Studio. Read more on Lifehacker and YourStory

Get in touch

Google Add-ons

Do more with your Gmail and GSuite account
G

We build bespoke solutions that use the capabilities and the features of Google Workspace for automating business processes and driving work productivity.

  1. Mail Merge with Attachments
    Send personalized email to your Google Contact with a Google Sheet and Gmail
  2. Save Emails and Attachments
    Download email messages and file attachments from Gmail to your Google Drive
  3. Google Forms Email Notifications
    Send email notifications to multiple people when a new Google Form is submitted
  4. Document Studio
    Create beautiful pixel perfect documents merging data from Google Sheets and Google Forms
  5. Creator Studio for Google Slides
    Turn your Google Slides presentations into animated GIFs and videos for uploading to YouTube