Why does your IF statement return "Yes" when both cells look empty? Why does =IF(A1<>B1,"Mismatch","OK") flag "100" and 100 as different? Why does it work in Excel Online but fail in desktop Excel with the same data?
The answer is simple: <> doesn’t handle blanks, number formatting, or leading spaces the way you think it does. And IF won’t warn you—it just returns garbage.
The Problem
You’re auditing vendor invoice amounts against purchase order records. Column A has PO amounts (as numbers). Column B has invoice amounts (some entered as text by finance staff who copy-paste from PDFs). You use this in C2: =IF(A2<>B2,"MISMATCH","MATCH"). It looks fine at first glance. Then you spot false positives—and worse, false negatives.
| PO ID | PO Amount (A) | Invoice Amount (B) | Current Formula Result (C) | Correct? |
|---|---|---|---|---|
| PO-7821 | 12450 | 12450 | MATCH | ✓ |
| PO-7822 | 8920 | "8920" | MISMATCH | ✗ |
| PO-7823 | " " | MISMATCH | ✗ | |
| PO-7824 | 45600 | 45600.00 | MATCH | ✓ |
| PO-7825 | 3210 | "3210 " | MISMATCH | ✗ |
| PO-7826 | 0 | "" | MISMATCH | ✗ |
| PO-7827 | 18750 | 18750 | MATCH | ✓ |
That’s 4 errors out of 7 rows. Not acceptable for audit trails. The <> operator treats numbers and text as fundamentally different—even when they *look* identical.
The Solution
Do this instead. In cell C2, enter:
=IF(EXACT(TRIM(A2),TRIM(B2)),"MATCH","MISMATCH")
This works because:
TRIM()removes leading/trailing spaces from both valuesEXACT()compares case-sensitive text—but crucially, it treats numbers and text *identically* when converted to strings- It returns
TRUEonly when both sides are byte-for-byte identical after trimming
Now drag that formula down from C2 to C8. Watch what happens.
| PO ID | PO Amount (A) | Invoice Amount (B) | Fixed Formula Result (C) |
|---|---|---|---|
| PO-7821 | 12450 | 12450 | MATCH |
| PO-7822 | 8920 | "8920" | MATCH |
| PO-7823 | " " | MATCH | |
| PO-7824 | 45600 | 45600.00 | MISMATCH |
| PO-7825 | 3210 | "3210 " | MATCH |
| PO-7826 | 0 | "" | MATCH |
| PO-7827 | 18750 | 18750 | MATCH |
Notice row 4 now correctly flags 45600 vs 45600.00 as a mismatch—that’s intentional. Decimal precision matters in finance. If you want those to match, wrap both sides in ROUND(...,0).
Going Further
You’ll need variations for real-world complexity. Here’s what works—and what doesn’t.
For dates: Use =IF(EXACT(TEXT(A2,"yyyymmdd"),TEXT(B2,"yyyymmdd")),"OK","DIFF"). Raw date serials differ across time zones and formatting. Convert to consistent string first.
For case-insensitive matching: Drop EXACT(). Use =IF(TRIM(UPPER(A2))=TRIM(UPPER(B2)),"OK","DIFF"). But be warned: this fails on numbers-as-text like "123" vs 123 unless you wrap both in TEXT(...,"@").
The counterintuitive tip: IF(ISERROR(XMATCH(A2,B:B,0)),"Not Found","Found") is faster than nested IFs when comparing one value against a full column. And it handles blanks cleanly. Try it in D2 with =IF(ISERROR(XMATCH(TRIM(A2),TRIM($B$2:$B$100),0)),"Missing","Present").
Don’t use NOT(A2=B2) as a replacement for A2<>B2. It behaves identically—and inherits all the same flaws.
When NOT to Use This
Don’t reach for EXACT(TRIM()) if either column contains formulas returning #N/A, #VALUE!, or other errors. EXACT() will return #N/A itself—and break your entire column.
Don’t use it on >50k rows without testing performance. EXACT() is single-threaded and slower than = comparison. For large datasets, pre-clean with Power Query: select both columns → right-click → “Transform” → “Trim” → then do simple =A2=B2.
Don’t apply it to columns with mixed data types where you actually *want* type-aware comparison. Example: You need to distinguish between the number 0 and the logical FALSE. EXACT() converts both to text and returns TRUE. That’s wrong. Keep <> there.
If your data includes Chinese or Arabic characters and you’re using Excel prior to version 2102, test thoroughly. Older versions have Unicode edge cases with TRIM() and EXACT().
Keyboard Shortcuts
Speed up editing and validation:
| Action | Shortcut | Notes |
|---|---|---|
| Edit active cell | F2 | Essential for checking formula logic before dragging |
| Select current region (Ctrl+* equivalent) | Ctrl+A | Press twice to select entire used range |
| Evaluate formula step-by-step | Alt+M+V | Critical for debugging mismatches in complex chains |
| Toggle formula view | Ctrl+` | See all formulas at once—spot hidden TRIM() omissions fast |
| Open Name Manager | Ctrl+F3 | Use to verify named ranges aren’t hiding text/number mismatches |
