Content authored by practitioners with experience at Amazon India, Intuit QuickBooks, and the Tata Group. Meet the team →
Bank and ledger amounts that look identical fail an exact-match test, so matched items show as differences.
Convert any text amounts to numbers, round both sides to two decimals, then test whether the absolute difference is within a tolerance held in one labelled cell, and send anything outside it to review.
Bank amount and ledger amount columns, a single tolerance cell such as ₹1, and a match-status formula using ABS and ROUND.
Paise-level rounding differences match automatically, while real differences such as TDS or bank charges go to review.
Two amounts can look the same and still not match. One may carry hidden decimals, or one may be stored as text, so an exact test such as =B2=C2 returns FALSE. Convert text to numbers, round both sides to two decimals, then compare within a set tolerance such as ₹1, kept in its own cell where everyone can see it.
Every formula here works in Excel 2010 and later, including Microsoft 365.
What are the likely causes?
| Cause | How to spot it | Fix |
|---|---|---|
| Hidden decimals | Add decimal places; 48,500.00 becomes 48,499.996 | =ROUND(B2,2) on both sides |
| Number stored as text | =ISNUMBER(B2) returns FALSE; value is left-aligned | =VALUE(SUBSTITUTE(TRIM(B2),”,”,"")) |
| Trailing spaces | =LEN(B2) is longer than the digits | TRIM inside VALUE |
| Dr or Cr suffix (Tally export) | “48,500.00 Dr” | Remove the suffix, then VALUE |
| Thousand separators as text | ”48,500” will not sum | SUBSTITUTE the comma away |
| Negative-sign conventions | Bank shows a debit as positive in a Debit column; ledger shows it as negative | Compare =ABS() of both, or flip the sign on one side |
Hidden decimals usually come from a formula: an amount worked out as a percentage, or GST calculated and not rounded. The cell shows ₹48,500.00, but Excel compares the full value.
How do I compare with a tolerance instead of exact equality?
Put the tolerance in one cell. Say H1 holds 1 and H2 has the label “Tolerance (₹), approved by FM”.
With bank amount in B and ledger amount in C:
=IF(ABS(ROUND(B2,2)-ROUND(C2,2))<=$H$1, "Match", "Review")If either side might be text, convert it first:
=IF(ABS(ROUND(VALUE(B2),2)-ROUND(VALUE(C2),2))<=$H$1, "Match", "Review")Keep the difference visible in its own column too:
=ROUND(B2,2)-ROUND(C2,2)The reviewer then sees not just “Review” but how much and in which direction.
Worked example: ₹48,500 against four ledger entries
Tolerance in H1 is ₹1. The bank shows a debit of ₹48,500.00. The ledger has four candidate entries.
| Pair | Bank (B) | Ledger (C) | Difference | Exact =B2=C2 | Tolerance formula |
|---|---|---|---|---|---|
| 1 | ₹48,500.00 | ₹48,499.60 | ₹0.40 | FALSE | Match |
| 2 | ₹48,500.00 | ₹48,500 (from a formula, true value 48,499.996) | ₹0.00 after rounding | FALSE | Match |
| 3 | ₹48,500.00 | ”48,500” stored as text | ₹0.00 after VALUE | FALSE | Match (second formula) |
| 4 | ₹48,500.00 | ₹47,530.00 | ₹970.00 | FALSE | Review |
Check pair 1: 48,500.00 less 48,499.60 is 0.40, which is within ₹1. Pair 2: 48,499.996 rounds to 48,500.00, so the difference is zero. Pair 3: VALUE turns the text into 48500. Pair 4: 48,500 less 47,530 is 970, far outside ₹1.
The exact test failed on all four. The tolerance formula matched three and sent one to review.
What should never be matched by tolerance?
Pair 4 is the warning. ₹970 is exactly 2 percent of ₹48,500, a common TDS rate on contractor payments. That is not rounding. It is a deduction that needs its own entry.
Never widen the tolerance to absorb:
- TDS: differences equal to 1, 2 or 10 percent of the amount.
- Bank charges: typical round amounts such as ₹5.90, ₹17.70 or ₹23.60, which often include 18 percent GST.
- Fees and discounts: gateway fees or an early-payment discount.
A tolerance should be small, written down and approved. If someone quietly changes ₹1 to ₹1,000, real differences start disappearing. That is how rounding becomes leakage.
What to read next
Next, read VLOOKUP, XLOOKUP or INDEX-MATCH: which to use to match two lists. For written tolerance rules and approvals at scale, see tolerance matching in reconciliation, and for pulling clean keys out of narrations, the bank narration parsing workbook.
TransactIG matches bank statement lines to your books within the tolerance you set, and sends anything outside it to an exception queue; see bank reconciliation software for India.