Ever needed to compare two lists in Google Sheets and find out what’s missing, duplicated, or changed? Whether you’re comparing employee names, product IDs, or any kind of dataset, Google Sheets gives you a few easy ways to highlight differences. This is especially useful for beginners managing lists that change over time, like inventory or customer data.
Instead of manually checking values line-by-line, Google Sheets lets you use formulas to automatically detect mismatches or missing values. It’s a simple way to spot errors and save a ton of time—especially when your lists are long or updated frequently.
Why Compare Two Columns in Google Sheets?
Here are a few real-world reasons why comparing columns is super helpful:
- Track who has left or joined a team by comparing old and new staff lists.
- Identify missing items between two inventory records.
- Find differences between two customer email lists.
- Check if data was updated correctly after importing from another system.
Real-Life Example: Comparing Inventory Lists
Let’s say you have two inventory snapshots from different days. You want to know which items were added or removed.
Snapshot 1 (Yesterday)
| Product ID (A) |
|---|
| 1001 |
| 1002 |
| 1003 |
| 1004 |
Snapshot 2 (Today)
| Product ID (B) |
|---|
| 1002 |
| 1003 |
| 1004 |
| 1005 |
Goal:
- Find what was removed: Exists in Column A but not in Column B.
- Find what was added: Exists in Column B but not in Column A.
Method 1: Use IF + ISERROR + MATCH to Find Differences
Find Removed Items (from Column A not in B)
=IF(ISERROR(MATCH(A2, B:B, 0)), "Removed", "")Find Added Items (from Column B not in A)
=IF(ISERROR(MATCH(B2, A:A, 0)), "Added", "")This checks if each value in Column A or B exists in the other. If not, it flags it.
Method 2: Use Conditional Formatting to Highlight Differences
Want a visual way to highlight differences?
- Select your first column (e.g., A2:A100).
- Go to Format → Conditional formatting.
- Choose Custom formula is and enter:
=ISERROR(MATCH(A2, B:B, 0))This will highlight values in A that aren’t in B.
Repeat for Column B using:
=ISERROR(MATCH(B2, A:A, 0))Method 3: Use FILTER to Extract Unique Values
To show a clean list of only the missing items:
Items in A but not in B
=FILTER(A2:A, ISERROR(MATCH(A2:A, B2:B, 0)))Items in B but not in A
=FILTER(B2:B, ISERROR(MATCH(B2:B, A2:A, 0)))This is great for reporting or generating lists of changes.
Bonus Tip: Use UNIQUE to Spot Duplicates
If you merge two lists into one and want to see only the unique values:
=UNIQUE({A2:A; B2:B})This combines both columns vertically and removes duplicates.
Quick Cheat Sheet
| Formula | Purpose |
|---|---|
| =IF(ISERROR(MATCH(A2, B:B, 0)), “Removed”, “”) | Flag values in A that aren’t in B |
| =IF(ISERROR(MATCH(B2, A:A, 0)), “Added”, “”) | Flag values in B that aren’t in A |
| =FILTER(A2:A, ISERROR(MATCH(A2:A, B2:B, 0))) | List values in A not in B |
| =UNIQUE({A2:A; B2:B}) | Merge both columns and remove duplicates |
Comparing two columns in Google Sheets doesn’t need to be a headache. With a few built-in formulas like MATCH, FILTER, and IF, you can easily find what’s changed between two lists. Whether you’re checking employee rosters, product inventories, or customer signups, these tricks will save you time and reduce human error.
Try these methods in your next spreadsheet and feel the difference. Happy comparing!