Logical & Lookup Formulas
Check if Multiple Columns Are Equal (Row Audit & SUMPRODUCT Count)
Verify whether three or more system export columns agree on every row using chained pairwise AND(B2=C2, B2=D2) tests and count fully synchronized rows across a table with SUMPRODUCT.
Why B2=C2=D2 fails in Excel—and how transitive pairwise equality works!
During ERP-to-warehouse-to-billing reconciliation, analysts often try writing =B2=C2=D2—only to watch Excel evaluate (B2=C2) into TRUE or FALSE first and then compare that boolean against D2, returning FALSE almost everywhere! In spreadsheet logic, testing whether K columns hold identical values on row 2 requires K - 1 pairwise comparisons anchored to the first column: =AND(B2 = C2, B2 = D2)!
To audit an entire 5-SKU reconciliation table without adding helper columns, multiply row-wise boolean arrays inside SUMPRODUCT: =SUMPRODUCT((B2:B6 = C2:C6) * (B2:B6 = D2:D6)). Across 5 SKUs where ERP, WMS, and Shopify stock counts match on 3 SKUs and drift on 2 SKUs, SUMPRODUCT returns 3 synchronized SKUs and 5 - 3 = 2 discrepancy rows!
| SKU Code | ERP Qty (Col B) | WMS Qty (Col C) | Storefront Qty (Col D) | AND(B=C, B=D) Audit |
|---|---|---|---|---|
| SKU-101 | 140 | 140 | 140 | Match (All 3 Equal) |
| SKU-102 | 85 | 85 | 82 | Mismatch (Storefront Drift) |
| SKU-103 | 210 | 205 | 210 | Mismatch (WMS Drift) |
Row-Level Multi-Column Agreement Check
Anchors every comparison to column B inside AND(B2 = C2, B2 = D2) and labels rows "Match" or "Mismatch".
=IF(AND(B2 = C2, B2 = D2), "Match", "Mismatch")Total Count of Rows Where All Three Columns Match
Multiplies pairwise equality arrays inside SUMPRODUCT to count rows where ERP, WMS, and Storefront agree simultaneously.
=SUMPRODUCT((B2:B6 = C2:C6) * (B2:B6 = D2:D6))Try the example
Use the original values or change them to test your understanding.