Is this statement line already in the ledger? (COUNTIF)
The problem
Problem statement
Reconciling a bank statement means matching each line to the ledger. The line to check is A2, and the ledger's references are in D2:D5. Put "Matched" or "Unmatched" in the Status cell B2.
The reference comes from A2 — the formula has to answer for whichever line it is given.
Problem data
Show the sheet as a table (selectable, copyable)
| A | B | D | |
|---|---|---|---|
| 1 | Statement line | Status | Ledger refs |
| 2 | TRX-1003 | TRX-1001 | |
| 3 | TRX-1002 | ||
| 4 | TRX-1004 | ||
| 5 | TRX-1006 |
Notes
COUNTIF answers "how many times does this reference appear?" and 0 means it never cleared the ledger. Comparing against a count rather than a lookup keeps it simple: you do not care which ledger row matched, only that one exists.
How to approach
Your answer goes in B2.
- Target cell is B2
- Count occurrences of A2 in the ledger refs
- Zero occurrences means unmatched
- Shape: =IF(COUNTIF(?, ?)=0, ?, ?)
Function guide
FAQ
How do I find bank lines that are not in the ledger?
=IF(COUNTIF(ledger_refs, line_ref)=0, "Unmatched", "Matched"), then filter the Unmatched rows. COUNTIFS does the same for a match on two keys, such as reference plus amount.
Why not VLOOKUP for reconciliation?
You can, but a lookup is heavier than needed and its #N/A needs wrapping. COUNTIF answers the actual question — does it exist — in one step.