Compare Two Sets of Multi-Column Data for Differences in Google Sheets

To compare two sets of multi-column data (two tables) for differences in Google Sheets, there must be a unique column in both tables.

For example, in an employee database, this could be the Employee ID or Employee Name. In a product database, it might be the Product Code or Product Name.

We’ll use an advanced technique involving a helper formula and conditional formatting to achieve this task. This method highlights cells in either table that differ from the corresponding cells in the other table.

What makes my approach exciting is that the records don’t need to be in the same order; the unique IDs in the two tables don’t have to match row by row.

We’ll utilize an advanced XLOOKUP formula to identify mismatched values in each record and use the formula’s output to highlight the relevant cells.

Let’s consider two employee databases as examples. We will compare these two multi-column datasets and identify differences, such as changes in salary, addition of new employees, and more.

Sample Data

Below is the sample data in Sheet1 with fields: Employee ID, Name, Department, Salary, and Joining Date:

Sample employee data in Sheet1, columns A to E

And here is the sample data in Sheet2 with the same field labels:

Sample employee data in Sheet2, columns A to E

We will use Employee ID as the unique identifier to compare these two sets of multi-column data for differences.

Step 1: Choosing the Table to Highlight

There are two tables, and you can choose which one you want to compare and highlight for differences. You can highlight differences in either or both tables.

Let’s start by highlighting the multi-column data in Sheet1 for differences.

Step 2: Formula to Compare Two Sets of Multi-Column Data

Navigate to cell G2 of Sheet1 and enter the following formula:

=ArrayFormula(IFNA(BYCOL(Sheet2!A2:E, LAMBDA(col, XLOOKUP(A2:A, Sheet2!A2:A, col)))=A2:E))
Comparing two sets of multi-column data for differences in Google Sheets

Explanation of the Formula:

  • A2:E: The range of the table in Sheet1.
  • Sheet2!A2:E: The range of the table in Sheet2.
  • A2:A: The unique ID column in Sheet1.
  • Sheet2!A2:A: The unique ID column in Sheet2.

This formula returns an array with TRUE or FALSE values:

  • FALSE indicates the value is different in the second table.
  • Empty rows indicate that the record is not present in the second table.

We’ll now use this array to highlight the differences.

Step 3: Highlighting Differences

  1. Select A2:E in Sheet1.
  2. Go to Format > Conditional Formatting.
  3. Under Format Rules, select Custom Formula is.
  4. Enter the following formula: =AND(LEN(A2), NOT(G2))
  5. Choose a fill color for highlighting.
  6. Click Done.

This will highlight the values in Sheet1 that don’t match the corresponding values in Sheet2.

Highlighting differences between two sets of multi-column data in Google Sheets

Compare and Highlight Differences in Both Tables

If you want to compare the data in Sheet2 with Sheet1 and highlight the differences:

  1. Enter the same formula in Sheet2!G2, modifying the range references:
    • Replace Sheet2!A2:E with Sheet1!A2:E.
    • Replace Sheet2!A2:A with Sheet1!A2:A.
  2. Apply the same conditional formatting rule in Sheet2 to highlight the differences.

Formula Breakdown

Here’s a detailed breakdown for those interested in learning the formula:

=ArrayFormula(IFNA(BYCOL(Sheet2!A2:E, LAMBDA(col, XLOOKUP(A2:A, Sheet2!A2:A, col)))=A2:E))
  1. XLOOKUP(A2:A, Sheet2!A2:A, Sheet2!A2:A): Looks up the Employee IDs in A2:A of Sheet1 within Sheet2!A2:A and returns the matching rows. (To test it, enter it as an array formula by wrapping it with the ARRAYFORMULA function.)
  2. Custom Function: To fetch values from all columns in Sheet2, use a LAMBDA function (by modifying the above XLOOKUP formula): LAMBDA(col, XLOOKUP(A2:A, Sheet2!A2:A, col))
  3. BYCOL(Sheet2!A2:E, ...): BYCOL applies the LAMBDA function to each column in Sheet2!A2:E.
  4. IFNA: Replaces #N/A errors (from mismatched rows) with blank values.
  5. =A2:E: Compares the fetched values from Sheet2 with the values in Sheet1. Returns TRUE for matches and FALSE for mismatches.

Additional Resources

Here are some more Google Sheets resources related to comparing values:

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.

Filter the Bottom 10 Items in a Pivot Table in Google Sheets

This tutorial explains how to filter the bottom 10 items in a Pivot Table...

Hyperlink to Jump to the Last Used Row in Excel

In a vertical range, you can create a hyperlink to jump to the last...

Find the Last Used Row’s Last Value Address in Excel

In a large vertical dataset in Excel, how do you find the cell address...

Find the Last Used Row Number in Excel

When working with large datasets such as sales records, purchase data, or bills of...

More like this

Filter the Bottom 10 Items in a Pivot Table in Google Sheets

This tutorial explains how to filter the bottom 10 items in a Pivot Table...

Cycle Highlights in Google Sheets – Rotate Highlights Daily

Want to cycle highlights in Google Sheets every day? Whether you're rotating a meal...

Filter Rows Containing Multiple Selected Values in Google Sheets

This tutorial explains how to filter rows in a column containing multiple selected drop-down...

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.