Why does your report show ‘Match’ when it shouldn’t? Why does =A1=B1 return FALSE even though the values look identical? Why does copying the same formula down Column C give inconsistent results?
The answer is almost always hidden whitespace, invisible characters, or mismatched data types — not your formula. Excel doesn’t care what something *looks* like. It cares what it *is*.
The Problem
You’re auditing vendor payments. Column A holds invoice IDs from your ERP system. Column B holds the same IDs entered manually by finance staff. You need to flag mismatches before payment processing — but scanning 472 rows by eye isn’t safe or scalable.
| Row | ERP ID (A) | Finance ID (B) | Manual Check |
|---|---|---|---|
| 2 | INV-2024-0876 | INV-2024-0876 | ✓ |
| 3 | INV-2024-0877 | INV-2024-0877 | ✗ (trailing space) |
| 4 | INV-2024-0878 | inv-2024-0878 | ✗ (case difference) |
| 5 | INV-2024-0879 | INV-2024-0879 | ✓ |
| 6 | INV-2024-0880 | INV-2024-0880 | ✗ (non-breaking space) |
| 7 | INV-2024-0881 | #N/A | ✗ (error) |
That’s 3 mismatches in 6 rows — all invisible to the naked eye. Your brain sees ‘same’. Excel sees different character codes.
The Solution
Use this single formula in C2 and drag down:
=EXACT(TRIM(CLEAN(A2)),TRIM(CLEAN(B2)))
It works because:
CLEAN()removes non-printable characters (like ASCII 160 — the sneaky non-breaking space in row 6)TRIM()strips leading/trailing spaces (row 3)EXACT()compares case-sensitively — no surprises (row 4)
Here’s what happens step-by-step:
| Step | Action | Result | Shortcut |
|---|---|---|---|
| 1 | In C2, type =EXACT(TRIM(CLEAN(A2)),TRIM(CLEAN(B2))) | FALSE (row 3) | — |
| 2 | Press Ctrl+Enter to keep focus in C2 | Formula stays active | Ctrl+Enter |
| 3 | Select C2, then drag fill handle down to C7 | All 6 comparisons calculated | Click + drag |
| 4 | Select C2:C7 → Home tab → Conditional Formatting → Highlight Cell Rules → Equal To → TRUE → Green fill | Matches glow green; mismatches stay plain | Alt+H+L+G |
| Row | ERP ID (A) | Finance ID (B) | =EXACT(TRIM(CLEAN(A2)),TRIM(CLEAN(B2))) |
|---|---|---|---|
| 2 | INV-2024-0876 | INV-2024-0876 | TRUE |
| 3 | INV-2024-0877 | INV-2024-0877 | TRUE |
| 4 | INV-2024-0878 | inv-2024-0878 | FALSE |
| 5 | INV-2024-0879 | INV-2024-0879 | TRUE |
| 6 | INV-2024-0880 | INV-2024-0880 | TRUE |
| 7 | INV-2024-0881 | #N/A | #N/A |
Notice row 7 returns #N/A — not FALSE. That’s correct. You can’t compare an error to text. Handle that separately (see next section).
Going Further
Real work isn’t just exact matches.
Partial match inside a longer string? Use =ISNUMBER(SEARCH(A2,B2)) — but know this is case-insensitive and fails if A2 is blank. Wrap it: =IF(A2="","",ISNUMBER(SEARCH(A2,B2))).
Match ignoring case but still catching whitespace? Skip EXACT. Use =TRIM(CLEAN(A2))=TRIM(CLEAN(B2)). Simpler. Faster. Less strict.
What if one cell is numeric and the other is text? Force both to text first: =TEXT(A2,"@")=TEXT(B2,"@"). Works for dates, numbers, and strings alike.
Surprising tip: EXACT() treats empty cells and zero-length strings (“”) as identical. But =""=A2 does not — it returns FALSE if A2 contains only spaces. Always use TRIM(CLEAN()) first if blanks are possible.
Need to highlight mismatches across two entire ranges? Select B2:B100 → Home → Conditional Formatting → New Rule → Use a formula → =EXACT(TRIM(CLEAN($A2)),$B2)=FALSE → Format red. Done.
When NOT to Use This
This approach fails silently in three cases:
- Dates formatted differently: 45231 (serial number) vs “2023-10-15” (text). Convert both to date serials first:
=EXACT(TEXT(A2,"yyyymmdd"),TEXT(B2,"yyyymmdd")) - Numbers with custom formatting: $45,200.00 vs 45200. Use VALUE() or double-unary:
=EXACT(--A2,--B2)— but only if both are truly numeric. - Cells containing formulas returning "":
=IF(A1>100,"OK","")looks empty but isn’t. Test withLEN(TRIM(CLEAN(A2)))=0, notA2="".
If your data includes hyperlinks, merged cells, or array formulas — stop. Clean the source first. No formula fixes dirty structure.
Keyboard Shortcuts
| Action | Windows Shortcut | Mac Shortcut |
|---|---|---|
| Open Conditional Formatting | Alt+H+L | Control+Option+Command+T |
| Insert Function Dialog | Shift+F3 | Shift+Command+A |
| Edit Cell Formula | F2 | Control+U |
| Fill Down (from active cell) | Ctrl+D | Command+D |
| Toggle Formula View | Ctrl+` (backtick) | Command+` |
