Two Lists That Should Agree: Comparing Columns, COUNTIF, MATCH and the 490.60 That Was Three Problems

Two exports, same fortnight, same fourteen payments. The sales ledger adds to 32,648.75. The bank adds to 33,139.35. Somebody has to explain the 490.60 by Thursday.

The instinct is to sort both lists and read them side by side. It works for twenty rows and fails silently for two thousand, and it fails in a particular way: the eye matches things that look alike, which is exactly the wrong test, because two of the five problems in this file are invisible to the eye. One ID has a trailing space. Another is a number on one side and text on the other. Both look perfect on screen and neither will ever match.

This article is about doing it properly — what each comparison technique actually compares, which ones lie to you and how, and how to end up with a variance column somebody else can check rather than a feeling that it is probably fine.

What you need. Sections 1 to 12 work in every version of Excel this century, and in Google Sheets with the noted exceptions. XLOOKUP, XMATCH and FILTER are Microsoft 365 and Excel 2021 or later. Power Query in section 13 is Excel 2016 and later on Windows, 2021 and later on Mac.

1) Two Exports, One Number That Does Not Agree

🎯 Scenario: Month-end. The ledger says 32,648.75 was invoiced and settled in the first three weeks of July. The bank statement for the same period totals 33,139.35. Nobody doubts either system. Somebody has to name the difference.

Start with the arithmetic that frames the job:

Fourteen rows on each side is the detail that sends most people down the wrong path. Equal counts feel like "nothing is missing, so something must be mistyped" — and here it means a row is missing from each side, which is not the same thing at all.

The gap is three separate problems that happen to add up to one number:

• 1,505.00 the bank received and the ledger never recorded.
• 960.40 the ledger invoiced and the bank never received.
• 54.00 where both systems have the row and disagree about the amount.

1,505.00 − 960.40 − 54.00 = 490.60. No single explanation was ever going to account for it, which is the first rule of reconciliation: a whole-file difference is a symptom, not a cause. You cannot divide 490.60 by anything useful. You can only find the rows.

2) Decide Which Question You Are Asking

Three different questions get called "comparing two lists", and they want three different formulas. Choosing wrongly is the most common reason a comparison produces nonsense.

A reconciliation is almost always question two followed by question three. The bank does not owe you its rows in your order, and it will never send them that way. Any formula that assumes it will is answering question one about data that has no positional meaning.

3) =A2=D2 Is Almost Never the Formula You Want

Type =A2=D2 in this file and fill it down and you get four TRUEs — rows 3, 8, 11 and 15, where INV-2288, INV-2293, INV-2296 and INV-2300 each happen to land opposite themselves. Four out of fourteen. That is worse than none, because it reads as partial agreement when it is pure coincidence: the formula compares positions, and these two lists are in different orders. Sort either column and the four TRUEs move somewhere else.

There is a real use for it — two versions of the same export, where row order is meaningful and you want to know what changed between Friday and Monday. For that job it is the right tool, and Excel has a keyboard shortcut for it: select both columns, F5 → Special → Row differences (Ctrl+\ on Windows), and Excel selects every cell in the second column that differs from its neighbour. Colour them and you have a change log in two keystrokes.

Before you trust = anywhere, know what it actually compares:

• Case is ignored. ="abc"="ABC" is TRUE. If case matters — product codes where ab12 and AB12 are different things — use =EXACT(A2,D2).
• Spaces are not. ="INV-2291"="INV-2291 " is FALSE. = trims nothing, ever.
• Text and numbers are different values. =("300412"=300412) is FALSE. This one catches people because both cells read 300412 on screen.
• An empty cell equals zero. If D9 is blank, =C9=D9 where C9 holds 0 returns TRUE. In a reconciliation that turns a missing figure into an agreed one. Guard it with =AND(C9<>"",C9=D9).

And one thing EXACT does not do: it does not distinguish text from numbers. =EXACT(300412,"300412") is TRUE, because EXACT converts both sides to text first. EXACT settles case. ISNUMBER settles type. They are different questions and you need both.

4) The Membership Test: COUNTIF

For question two — what is on one list and not the other — the workhorse is COUNTIF. Put this in H2 and fill it to H15:

=IF(COUNTIF($D$2:$D$15, A2)=0, "not in bank", "")

It reads exactly as it sounds: how many times does this ledger reference appear anywhere in the bank column? Zero means the bank has never heard of it. The dollar signs matter — the range being searched must not drift as the formula fills down, while the reference being looked for must. Get that backwards and every row checks row 2.

Run the same thing the other way, in J2, filled to J15:

=IF(COUNTIF($A$2:$A$15, D2)=0, "not in ledger", "")

Both directions are compulsory. A single-direction check is how a duplicate payment lives in a bank account for six months: every ledger row is present in the bank, so the reconciliation looks clean, and the extra bank row nobody looked for is never seen.

On this file the ledger-side column flags two rows:

Two flags, and only one of them is money. That ratio is normal. Most of what a first-pass comparison reports is not missing data — it is the same data spelled two ways.

5) The Four Look-Alikes That Fake a Missing Row

INV-2291 and INV-2291 are different values to every comparison tool in Excel. They are the same value to every human who reads them. This is the entire problem with comparing lists, and it has four common causes.

1. Whitespace. A leading or trailing space from a copy-paste, a fixed-width export, or a name field somebody typed with the space bar. Invisible on screen and fatal to a match.

2. The non-breaking space, CHAR(160). Anything that has passed through a web page or an HTML email carries them. TRIM does not remove CHAR(160) — it only handles the ordinary space, CHAR(32) — which is why "I already trimmed it" is not the reassurance people think it is.

3. Text versus number. 0300412 in a column formatted as text, 300412 as a real number in the other file. The leading zero is the giveaway when there is one; when there is not, nothing on screen tells you, except the default alignment — text sits left, numbers sit right.

4. Case and stray punctuation. ab12 versus AB12; ACME Ltd. versus ACME Ltd; a hyphen that is actually an en dash pasted from a document.

The diagnostics take one row each and answer the question outright:

Then fix it once, in a key column, rather than fixing it inside every formula that touches the data:

=TRIM(SUBSTITUTE(CLEAN(A2), CHAR(160), " "))

CLEAN strips control characters (codes 0–31, the debris of a bad export), SUBSTITUTE converts non-breaking spaces to ordinary ones, and TRIM removes leading and trailing spaces plus repeated internal ones. If case is noise rather than signal, wrap the lot in UPPER. If one side is text and the other numeric, force a single type: =A2&"" makes everything text, =--A2 makes everything numeric and returns #VALUE! on anything that is not.

Build that column on both lists, compare the keys instead of the raw cells, and the trailing-space class of false positive disappears for good. It is also self-documenting: a reviewer can see what you normalised, which is not true of a fix buried three arguments deep in a lookup.

6) COUNTIF Has Traps of Its Own

COUNTIF is the right first tool and it is not a literal comparison. Four behaviours matter when you are using it to decide whether something exists.

It ignores case. =COUNTIF($D$2:$D$15,"inv-2288") returns 1. Usually convenient, occasionally a false match. The case-sensitive count is:

=SUMPRODUCT(--EXACT($D$2:$D$15, A2))

It treats wildcards as wildcards. *, ? and ~ in the criteria are pattern characters, not literal ones. A product code like AB-1?0 will match things it should not, and a SKU containing * matches nearly everything. Escape them with a tilde: AB-1~?0. If your identifiers can contain wildcards, do not use COUNTIF at all — use SUMPRODUCT(--EXACT(...)) or MATCH, neither of which pattern-matches.

It converts digits to numbers. A criterion of "0300412" is parsed as the number 300412 before the comparison happens. That is why the ledger's text 0300412 does not get flagged as missing even though the bank stored it as a number — COUNTIF quietly agreed that the two are the same value. Nothing else in your workbook will agree with it.

It compares only the first 15 significant digits. Numeric identifiers longer than 15 digits — card numbers, some bank references, certain national IDs — are indistinguishable to COUNTIF after the fifteenth digit. Two different accounts count as the same one. Store long IDs as text and compare them with EXACT.

7) MATCH, ISNA and XLOOKUP

COUNTIF answers "how many". MATCH answers "where", and its failure is an explicit #N/A rather than a zero:

=IF(ISNA(MATCH(A2, $D$2:$D$15, 0)), "not in bank", "row " & MATCH(A2, $D$2:$D$15, 0))

That third argument, 0, is not optional. Left out, MATCH assumes an approximate match on sorted data and returns confident nonsense on an unsorted list — the single most expensive default in Excel. XMATCH reverses it: exact is the default, and XMATCH(A2,$D$2:$D$15,0,-1) searches from the bottom when you want the last occurrence rather than the first.

Now run MATCH down the ledger and compare it with what COUNTIF said:

COUNTIF finds two problems, MATCH finds three, and MATCH is the one to believe. COUNTIF converted the text 0300412 to the number 300412 and declared a match. MATCH in exact mode converts nothing, so a text reference and a numeric one are two different values — which is also what XLOOKUP, =, a PivotTable, a Power Query merge and the database this eventually loads into will all conclude. The lenient tool is not being helpful here. It is hiding the one row that will break every join downstream.

XLOOKUP does presence and value in a single pass, which is what you actually want next:

=XLOOKUP(A2, $D$2:$D$15, $F$2:$F$15, "not in bank", 0)

Fourth argument if_not_found, fifth argument match_mode 0 for exact. Stuck on an older build, the same thing is:

=IFNA(INDEX($F$2:$F$15, MATCH(A2, $D$2:$D$15, 0)), "not in bank")

Use IFNA, not IFERROR. IFNA catches "not found" and nothing else. IFERROR also swallows #REF! from a deleted column, #VALUE! from a broken argument and #NAME? from a typo, relabelling all of them "not in bank". A reconciliation that reports missing rows that are not missing is worse than one that breaks loudly.

8) Both Directions, and the Count That Proves You Looked

Two anti-joins, one each way. In Microsoft 365 each is a single formula that spills its own answer:

=FILTER($A$2:$A$15, COUNTIF($D$2:$D$15, $A$2:$A$15)=0, "All ledger rows matched")
=FILTER($D$2:$D$15, COUNTIF($A$2:$A$15, $D$2:$D$15)=0, "All bank rows matched")

COUNTIF handed a whole column as its criteria returns one count per row, so FILTER gets the array of TRUE/FALSE it needs. The third argument matters as much as the first two: without it, a clean reconciliation returns #CALC!, which looks like a broken formula rather than good news.

Then prove the comparison was complete, with arithmetic rather than confidence:

The point of the third line is not the number. It is that matched plus unmatched must equal total, on both sides, every time. When it does not, your ranges are the wrong size — usually because the export gained rows and $D$2:$D$15 did not. Convert both lists to real Excel Tables (Ctrl+T) and the ranges grow with the data, and the formulas stop being a maintenance job.

9) The 1,240.00 That Matched Twice

🎯 Scenario: A customer pays two invoices of the same value in the same week. The reconciliation ticks both off against one bank line, reports a clean match, and the second payment sits unallocated for a quarter.

There are two invoices for 1,240.00 in the ledger: INV-2287 and INV-2292. Both are on the bank side too, so this file reconciles — but change the story slightly, so that the bank cleared only one of them, and a comparison built on amounts instead of references would report both as matched. Every lookup in Excel returns the first match and says nothing about the second.

So before matching anything, ask whether your key is unique. On both sides:

=SUMPRODUCT(--(COUNTIF($A$2:$A$15,$A$2:$A$15)>1))

Zero means the ledger references are unique and one-to-one matching is safe. Anything else and you have a choice to make, because a duplicate key has no single right answer:

Number the copies. An instance key turns duplicates into distinct values:

=A2 & "-" & COUNTIF($A$2:A2, A2)

The expanding range $A$2:A2 — anchored at the top, open at the bottom — counts occurrences so far, so the first INV-2287 becomes INV-2287-1 and a second becomes INV-2287-2. Build the same key on both sides and two payments against one invoice match two ledger lines in order, instead of both matching the first.

Or match at the level where the key is unique. If the bank sends one settlement line for three invoices, no per-row match exists at all, and the honest comparison is by total:

=SUMIF($A$2:$A$15, H2, $C$2:$C$15) - SUMIF($D$2:$D$15, H2, $F$2:$F$15)

with H2 holding the customer, week or batch that both sides share. Aggregating to the level where both systems agree is not a workaround; it is usually the correct reconciliation, and it is the one that survives a payment being split or combined next month.

10) When the Key Is Two Columns

Sometimes no single column identifies a row: a delivery is date plus depot, a timesheet line is person plus project. Two ways to handle it.

A composite key, on both sides:

=TEXT(B2,"yyyy-mm-dd") & "|" & TRIM(UPPER(A2))

Two rules make this safe. Force dates through TEXT with an explicit format — a raw date is a serial number and =B2&"|"&A2 will happily produce 46204|INV-2287, which is fine until the other file's dates are text and produce 03/07/2026|INV-2287 instead. And always use a separator that cannot occur in the data. Without one, "AB"&"C" and "A"&"BC" are both ABC, and two different rows collide into one key. The pipe is the usual choice because it is rare in real data; a hyphen is a bad choice in a file full of invoice numbers.

Or skip the helper column and use the plural functions, which take criteria pairs directly:

=COUNTIFS($D$2:$D$15, A2, $E$2:$E$15, B2)
=SUMIFS($F$2:$F$15, $D$2:$D$15, A2, $E$2:$E$15, B2)

Cleaner to read, and no extra columns — but note what it would do to this file. Every matched row here has a different ledger date and bank date, because a payment clears days after the invoice is raised. A key of ref-plus-date would report all fourteen rows as missing. Choose keys that both systems agree on, which usually means the identifier and not the timestamp.

11) Matching Amounts, Not Just Keys

Rows that exist on both sides can still disagree. Bring the other side's amount over and subtract:

=IFNA(XLOOKUP(A2,$D$2:$D$15,$F$2:$F$15,,0) - C2, "no bank row")

On this file that column reads 0.00 on every row XLOOKUP can find, with one exception: INV-2288 shows -54.00 — the ledger says 4,571.00, the bank paid 4,517.00. Two digits swapped. The three rows it cannot find (INV-2291, 0300412, INV-2297) come back as no bank row, and two of those three are spelling, not money — which is why the normalising pass in section 5 comes before this column, not after it.

Three things to get right in a variance column:

Round before you compare. Excel stores numbers in binary floating point, so quantities that came from different calculations can differ by 0.0000000001 and show as identical to the penny. =ROUND(x,2)=ROUND(y,2) is the reliable equality test for money, or accept a tolerance:

=IF(ABS(bank-ledger)<=0.005, "agreed", "check")

Flag by size, not by zero. =IF(variance<>0,"check","") marks every rounding artefact in a large file. A threshold — anything over half a penny, or over 1% of the ledger value — puts the reviewer's attention where the money is.

Let the difference name the cause. Three patterns identify themselves:

=MOD(variance,9)=0 on the difference column costs nothing and answers the "is this a typo or a missing invoice" question before anybody opens the other system.

And dates need the same care as amounts. =ISNUMBER(B2) tells you whether a date is a real date or text that looks like one; text dates never match real ones, and 01/07/2026 means two different days depending on which country's regional settings wrote the file. Compare dates as serial numbers, or not at all.

12) Seeing It Instead of Reading It

A column of flags is the audit trail. Colour is how a human finds the row in three seconds.

Select A2:A15, then Home → Conditional Formatting → New Rule → Use a formula:

=COUNTIF($D$2:$D$15, $A2)=0

Note the mixed reference: $A2 — column locked, row free — so the rule tests each row against its own cell as it fills down. This is the single most common conditional-formatting bug in the world: anchor the row as well and every cell in the range gets coloured according to row 2.

Excel's built-in Highlight Cells Rules → Duplicate Values looks like a shortcut for this, and it has two limits worth knowing before you rely on it. It is case-insensitive, so AB12 and ab12 are highlighted as duplicates of each other. And it marks duplicates within the selection as well as across it, so selecting both columns highlights the two 1,240.00 ledger rows for being duplicates of each other, which is not what you asked. It is a good ten-second look and a bad reconciliation.

A colour cannot be totalled, filtered on reliably by anyone other than you, or explained in a footnote next month. Colour for the eye, a column for the record — and if only one of them survives into the file you send on, make it the column.

13) When the Lists Arrive Every Week: Power Query

🎯 Scenario: The comparison you did once in July is now a Monday-morning job, and the bank's export gained four rows over the weekend that the formulas' fixed ranges know nothing about.

Everything above is a one-off comparison. The moment this becomes a Monday-morning job, formulas are the wrong shape: someone has to re-drag them over a new export, with the ranges silently wrong the first week the bank sends more rows.

Load both lists as queries (Data → From Table/Range), then Home → Merge Queries, pick the key column in each, and choose the join kind:

Two anti-join queries and an inner join answer this entire article, and next month the job is Refresh All.

Two things to do inside the query first, because Power Query is the strictest matcher in this whole toolkit:

• Normalise the key: Transform → Format → Trim and Clean, on both sides, before merging. The comparison happens on the value as stored, and no trailing space is forgiven.
• Set the data types to agree. Text 0300412 and whole-number 300412 produce no match at all, and Power Query does not warn you — the merged column simply comes back empty. A join that returns nothing is nearly always a type mismatch, not missing data.

The Power Query article on this blog covers the editor in full; this is only the reconciliation corner of it.

14) Which Technique to Use

Above the table, one rule: normalise first, compare second. Every technique in this article gives the wrong answer on unnormalised keys, and gives it confidently.

Practice

Rebuild the grid above — ledger in A1:C15, bank in D1:F15, with the trailing space really in D4 and D6 really a number — then work through these.

1. The gap. Total both amount columns and confirm 32,648.75, 33,139.35 and a difference of 490.60. Then find the three rows that explain it, and check they add back to 490.60 exactly.
2. The wrong question. Fill =A2=D2 down the file and count the TRUEs. Explain in one sentence why the answer would change if somebody sorted column D.
3. Both directions. Write the two COUNTIF flag columns. Note which rows each one catches, and which row appears on neither.
4. The disagreement. Add an ISNA(MATCH(...)) column beside the ledger COUNTIF column. Find the row where they disagree, and use ISNUMBER on both spellings of that reference to prove which one is right.
5. The invisible character. Use LEN and CODE(RIGHT(...,1)) on D4 to identify what is on the end of it. Then build the key column from section 5 on both lists and re-run the comparison: two flags should become one.
6. The proof. Write the matched / unmatched / total triple from section 8 for both sides and confirm each pair adds to 14. Then delete a bank row and watch which of the three numbers moves.
7. The transposition. Build the variance column with XLOOKUP, find the -54.00, and test =MOD(54,9)=0. Then break INV-2296 by typing 5,240.00 on the bank side and check whether the same test still identifies it.
8. The duplicate. Add a second bank line of 1,240.00 with no reference, then try to reconcile on amount alone and watch it match INV-2287 twice. Rebuild it with the instance key from section 9.

Summary

Comparing two lists looks like a formula problem and is really a data problem. The formulas are short — COUNTIF for membership, MATCH for strict presence, XLOOKUP for presence and value together — and none of them is where the time goes. The time goes into the fact that INV-2291 and INV-2291 are the same invoice to a person and two different values to a spreadsheet, and that 0300412 and 300412 are the same account number to everybody except the software.

So the order of work is fixed: normalise, then compare, then reconcile. A key column built with TRIM(SUBSTITUTE(CLEAN(...),CHAR(160)," ")) on both sides, with the types forced to agree, removes most of what a first pass would report. Only then does a comparison column mean anything — and it has to run in both directions, because a row missing from the ledger is a different problem from a row missing from the bank, and only one of them is visible from either side.

Trust the strict tools over the convenient ones. COUNTIF is the fastest thing to type and it ignores case, honours wildcards, converts digits to numbers, and stops distinguishing numeric IDs after fifteen digits. Every one of those behaviours turns a mismatch into a silent match, which is the direction of error that costs money. When COUNTIF and MATCH disagree about a row, MATCH is telling you what every other system will conclude.

And check that duplicates cannot ambush you before you match a single row. SUMPRODUCT(--(COUNTIF(range,range)>1)) on both key columns takes ten seconds and decides the whole approach: unique keys mean a one-to-one match, duplicates mean an instance key or an aggregate. Two invoices for 1,240.00 are not a problem until something tries to pair them with one bank line, and by then the reconciliation has already told you it balances.

Finally, know when to stop writing formulas. A comparison you will do once is a column of COUNTIFs and twenty minutes. A comparison you will do every Monday for the next two years is two anti-join queries and a Refresh button — and the difference between those two answers is not skill, it is how many more times somebody has to do this.