Anti-Join in Google Sheets: Find Unmatched Records Easily

To find unmatched records similar to an anti-join, you can use a combination of FILTER, NOT, IFNA, and XMATCH functions in Google Sheets. Google Sheets doesn’t directly support anti-joins as a built-in function.

An anti-join, also called a left anti-join, aims to find records in the left table that have no matching records in the right table. In contrast, a left join retrieves records from the left table and the matching records from the right table.

We will follow the approach below to find unmatched rows in Google Sheets:

  • XMATCH: Match the unique IDs in the left table with those in the right table. This function returns the relative position for matches and #N/A for mismatches.
  • IFNA: Replace #N/A errors from XMATCH with a more manageable value.
  • NOT: Return TRUE for mismatches and FALSE for matches.
  • FILTER: Filter the rows where the condition is TRUE.

Anti-Join with One Column: Find Unmatched Records Based on a Single Column

In the following example, the first table contains employee IDs, their names, and their department IDs. The data range is A1:C5.

The second table contains department IDs and department names. The data range is E1:F4.

Match the department IDs in the first table (left table) with those in the second table (right table) and find the unmatched records in the first table.

Anti-Join with One Column: Find Unmatched Records Based on a Single Column

Formula:

=LET(table1, A2:C5, table1ID, C2:C5, table2ID, E2:E4, FILTER(table1, NOT(IFNA(XMATCH(table1ID, table2ID)))))

When you use this formula, replace A2:C5 with the reference for the left table, C2:C5 with the column reference representing values from the left table that will be matched against the right table, and E2:E5 with the column reference in the right table containing values to match.

This example demonstrates how to perform an anti-join with one column in Google Sheets. It is useful for finding unmatched records by matching values in a unique identifier column in both tables.

Formula Breakdown

FILTER(table1, NOT(IFNA(XMATCH(table1ID, table2ID))))

This follows the FILTER function syntax FILTER(range, condition1, [condition2, …]).

Where:

  • range: table1, i.e., the left table range.
  • condition: NOT(IFNA(XMATCH(table1ID, table2ID)))

Explanation:

  • XMATCH: Matches values in table1ID with table2ID and returns the relative position for matches and #N/A for mismatches.
  • IFNA: Replaces #N/A errors with empty values.
  • NOT: Converts relative positions (from XMATCH) to FALSE and empty values (returned by IFNA) to TRUE.
  • FILTER: Filters rows where the condition NOT(IFNA(XMATCH(table1ID, table2ID))) is TRUE, indicating unmatched records.

This formula extracts rows in table1 that do not have corresponding matches in table2, based on the specified columns (table1ID and table2ID).

We have assigned names to ranges and used those names in the formula, made possible by the LET function.

Anti-Join with Two Columns: Find Unmatched Records Based on Multiple Columns

This time, we have student IDs, their names, and subjects in the left table, and student IDs, subjects, and grades in the right table. The table ranges are A1:C5 and E1:G3, respectively.

Here, we aim to match student IDs and subjects from the left table with those in the right table and extract the mismatched records.

Anti-Join with Two Columns: Find Unmatched Records Based on Multiple Columns

Formula:

=LET(table1, A2:C5, table1ID, TRANSPOSE(QUERY(TRANSPOSE({A2:A5, C2:C5}), ,9^9)), table2ID, TRANSPOSE(QUERY(TRANSPOSE({E2:E3, F2:F3}), ,9^9)), FILTER(table1, NOT(IFNA(XMATCH(table1ID, table2ID)))))

When you use this formula to find unmatched records based on multiple columns, make the following changes:

  • Replace A2:C5 with the reference for the left table.
  • Replace the ranges in the array {A2:A5, C2:C5} with the references to the columns in the left table. Values in these columns will be concatenated for matching in the right table.
  • Replace the ranges in the array {E2:E3, F2:F3} with the references to the columns in the right table. Values in these columns will be concatenated for matching.

If you have another identifier column or additional columns to include in the matching process, you can specify them within the respective arrays as needed.

Formula Explanation

This formula aligns with our previous example demonstrating how to perform an anti-join using one column for matching.

The key distinction here lies in defining table1ID and table2ID.

In this scenario, we execute an anti-join based on multiple columns, utilizing XMATCH for matching purposes, even though it doesn’t inherently support multi-column matching.

To accomplish this, we employ the TRANSPOSE-QUERY-TRANSPOSE combination to concatenate columns for comparison.

table1ID is derived from TRANSPOSE(QUERY(TRANSPOSE({A2:A5, C2:C5}), ,9^9)).

The TRANSPOSE function adjusts the data orientation, converting rows into columns. The QUERY function concatenates values from each column into a single combined value, treating all values as headers. Another TRANSPOSE reverts the orientation, resulting in a single-column output.

The same process applies to table2ID, which transforms two columns into a single combined column for comparison.

Resources

We have explored the anti-join (also known as left anti-join) in Google Sheets. Here are some resources related to joining two tables in Google Sheets:

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Hierarchical Numbering Sequences in Excel

Creating hierarchical numbering sequences in an Excel spreadsheet can significantly improve the way you...

How to Easily Repeat a Sequence of Numbers in Excel

Excel offers multiple ways to accomplish tasks, and the simplicity of each approach depends...

Create a Sequence of Dates at Every Nth Row in Excel (Dynamic Array)

Would you like to create a sequence of dates in every nth row in...

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

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