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

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

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.


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.

   "SELECT A WHERE A <> ''
    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.

Amit Agarwal

Amit Agarwal

Google Developer Expert, Google Cloud Champion

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.

Amit has developed several popular Google add-ons including Mail Merge for Gmail and Document Studio. Read more on Lifehacker and YourStory


Awards & Titles

Digital Inspiration has won several awards since it's launch in 2004.

Google Developer Expert

Google Developer Expert

Google awarded us the Google Developer Expert award recogizing our work in Google Workspace.

ProductHunt Golden Kitty

ProductHunt Golden Kitty

Our Gmail tool won the Lifehack of the Year award at ProductHunt Golden Kitty Awards in 2017.

Microsoft MVP Alumni

Microsoft MVP Alumni

Microsoft awarded us the Most Valuable Professional (MVP) title for 5 years in a row.

Google Cloud Champion

Google Cloud Champion

Google awarded us the Champion Innovator title recognizing our technical skill and expertise.

Email Newsletter

Sign up for our email newsletter to stay up to date.

We will never send any spam emails. Promise.