The first thing most people do when they need to test inequality is type =A1<>B1 and call it done. That’s not wrong — but it’s almost always incomplete. Because <> doesn’t tell you what to do when values differ. It just spits out TRUE or FALSE like a robot refusing to explain itself. And in real workbooks — where Sarah Chen at Acme Corp needs to flag mismatched invoice amounts or track overdue status changes — that binary output is useless without context.
The Setup
We’re working with a vendor reconciliation sheet from Alibaba’s internal finance team. It contains 9 rows of live data pulled weekly from ERP exports. Columns include Vendor Name (A), Expected Payment Date (B), Actual Payment Date (C), Invoice Amount (D), and Status (E). The goal: identify entries where actual payment date doesn’t match expected — but also highlight which ones are late vs. early, and exclude voided invoices.
| A | B | C | D | E |
|---|---|---|---|---|
| Alpha Logistics | 2024-03-15 | 2024-03-15 | $12,450 | Paid |
| Beta Systems | 2024-03-18 | 2024-03-22 | $8,920 | Paid |
| Cygnus Tech | 2024-03-20 | 2024-03-17 | $15,600 | Paid |
| Delta Med | 2024-03-22 | 2024-03-22 | $6,340 | Paid |
| Epsilon Labs | 2024-03-25 | 2024-04-02 | $22,100 | Paid |
| Fusion Global | 2024-03-28 | #N/A | $9,750 | Pending |
| Grove Solutions | 2024-03-30 | 2024-03-30 | $11,200 | Void |
| Horizon Inc | 2024-04-01 | 2024-04-05 | $18,400 | Paid |
| Indigo Partners | 2024-04-03 | 2024-03-29 | $7,250 | Paid |
The Challenge
You can’t just slap =B2<>C2 into column F and walk away. Why? Three reasons:
- Row 6 has
#N/Ain C2 —B6<>C6returnsTRUE, even though that’s not a valid date comparison. - Row 7 says “Void” in column E — we shouldn’t flag mismatches for voided entries, but
<>doesn’t know about business logic. - Even if
B2<>C2is TRUE, it doesn’t tell us whether payment was early (C2 < B2) or late (C2 > B2) — critical for follow-up.
The beauty of this approach is that <> isn’t the solution — it’s the starting point. What makes this elegant is layering it inside functions that *act* on the inequality, not just report it.
Walking Through It
We’ll build a robust mismatch flag in column F — then extend it to classify timing and filter cleanly. Start in cell F2.
| Step | Action | Result | Shortcut |
|---|---|---|---|
| 1 | Type =AND(ISNUMBER(B2),ISNUMBER(C2),B2<>C2) | Returns TRUE only if both dates exist AND differ | Enter |
| 2 | Wrap with IF: =IF(AND(ISNUMBER(B2),ISNUMBER(C2),B2<>C2),"MISMATCH","OK") | Now shows readable labels instead of TRUE/FALSE | Ctrl+Enter |
| 3 | Add status exclusion: =IF(OR(E2="Void",E2="Pending"),"SKIP",IF(AND(ISNUMBER(B2),ISNUMBER(C2),B2<>C2),"MISMATCH","OK")) | Skips Void/Pending rows entirely | Alt+= (to insert SUM, then edit) |
| 4 | Replace "MISMATCH" with logic: IF(C2 | Now gives actionable timing insight | F2 → arrow keys → Ctrl+Shift+Enter (if legacy) |
Here’s what happens after each step in row 2 (Alpha Logistics):
• Step 1: =AND(ISNUMBER(B2),ISNUMBER(C2),B2<>C2) → FALSE (dates match)
• Step 2: IF(...,"MISMATCH","OK") → "OK"
• Step 3: Adds OR(E2="Void",...) — still "OK" since E2 is "Paid"
• Step 4: Final formula yields "ON TIME" because B2 = C2
For row 3 (Beta Systems): B3=2024-03-18, C3=2024-03-22 → Step 4 returns "LATE".
For row 5 (Epsilon Labs): B5=2024-03-25, C5=2024-04-02 → also "LATE".
For row 9 (Indigo Partners): B9=2024-04-03, C9=2024-03-29 → "EARLY".
Surprising tip: You can use <> with text *and* numbers in the same condition — but only if Excel interprets them consistently. Try ="apple"<>"Apple" — it returns TRUE (case-sensitive). But =A1<>"PAID" where A1 contains “paid” (lowercase) will also return TRUE. Case sensitivity is baked in — no extra function needed.
The Result
Column F now delivers precise, business-ready insight — not just “different”, but *how* different and *whether it matters*. Here’s the final output for rows 1–9:
| A | B | C | D | E | F |
|---|---|---|---|---|---|
| Alpha Logistics | 2024-03-15 | 2024-03-15 | $12,450 | Paid | ON TIME |
| Beta Systems | 2024-03-18 | 2024-03-22 | $8,920 | Paid | LATE |
| Cygnus Tech | 2024-03-20 | 2024-03-17 | $15,600 | Paid | EARLY |
| Delta Med | 2024-03-22 | 2024-03-22 | $6,340 | Paid | ON TIME |
| Epsilon Labs | 2024-03-25 | 2024-04-02 | $22,100 | Paid | LATE |
| Fusion Global | 2024-03-28 | #N/A | $9,750 | Pending | SKIP |
| Grove Solutions | 2024-03-30 | 2024-03-30 | $11,200 | Void | SKIP |
| Horizon Inc | 2024-04-01 | 2024-04-05 | $18,400 | Paid | LATE |
| Indigo Partners | 2024-04-03 | 2024-03-29 | $7,250 | Paid | EARLY |
What Could Go Wrong
Three mistakes I see daily — all triggered by overconfidence in <>:
Mistake #1: Comparing text that looks like numbers
You have "123" in A1 (as text) and 123 in B1 (as number). =A1<>B1 returns FALSE — Excel auto-converts and treats them as equal. But if A1 contains " 123" (with space), it returns TRUE. Always wrap suspect cells in VALUE() or use TRIM() first. Test with =ISTEXT(A1).
Mistake #2: Using <> inside SUMIFS with empty strings
You write =SUMIFS(D2:D10,E2:E10,
