To compare two columns in Excel row by row, put =IF(A2=B2, "Match", "Different") in cell C2 and fill it down. To check whether each value in column A appears anywhere in column B, use =IF(COUNTIF(B:B, A2) > 0, "Found", "Missing") instead. The first compares cells side by side; the second compares lists. Picking the wrong one is why people get answers that look wrong when the formula is perfectly correct.
This is the everyday check behind reconciliation. You converted a statement or a report out of a PDF and now you need to know whether the rows you extracted match the rows in your accounting system, whether any invoice numbers went missing, and whether any of them got duplicated on the way through.
Row by row versus list against list
Decide which question you are actually asking before you write a formula, because the two are not the same.
| Question | Formula | What it tells you |
|---|---|---|
| Do A2 and B2 hold the same value? | =IF(A2=B2, "Match", "Different") | Row by row. Order matters |
| Does A2 exist anywhere in column B? | =IF(COUNTIF(B:B, A2) > 0, "Found", "Missing") | Membership. Order does not matter |
| How many times does A2 appear in B? | =COUNTIF(B:B, A2) | Catches duplicates as well as gaps |
| Which row of B holds A2? | =MATCH(A2, B:B, 0) | The position, or #N/A if it is absent |
| Same value, same letter case? | =EXACT(A2, B2) | Case sensitive comparison |
The plain equals sign is case insensitive, so "ACME Corp" and "acme corp" both come back as a match. That is usually what you want with vendor names. When it is not, EXACT is the only comparison in Excel that respects letter case.
How do I find values in one column that are missing from another?
Use COUNTIF against the other column and look for zero. In C2, write =IF(COUNTIF($B$2:$B$500, A2) = 0, "Missing from B", "") and fill it down. Every row that comes back with a label is a value in A that column B does not contain anywhere. Then run the reverse in another column, =IF(COUNTIF($A$2:$A$500, B2) = 0, "Missing from A", ""), because a list can be short on one side and long on the other at the same time, and checking only one direction hides half the problem.
Lock the ranges with dollar signs. Without them the range slides down as you fill, and the last rows of the comparison list drop out of scope, which produces false Missing results at the bottom of the sheet. That is a genuinely nasty bug because the answers look plausible.
MATCH does the same job and tells you where: =MATCH(A2, $B$2:$B$500, 0) returns the row position, or #N/A when the value is absent. Wrapping it in ISNA gives a clean flag: =IF(ISNA(MATCH(A2, $B$2:$B$500, 0)), "Missing", "Present").
How do I highlight the differences between two columns?
Conditional formatting shows the answer without adding a helper column. Select both columns, go to Home, then Conditional Formatting, then Highlight Cells Rules, then Duplicate Values. In the dialog switch the dropdown from Duplicate to Unique, and everything appearing in only one of the two columns lights up. That single click is often all a reconciliation needs.
For a row by row comparison, use a formula rule instead. Select A2 down to B500, choose Conditional Formatting, then New Rule, then Use a formula to determine which cells to format, and enter =$A2 <> $B2. Pick a fill color and every row where the two cells disagree gets highlighted. The single dollar sign on the column is what makes the rule read across the row rather than locking to one cell.
Why does my comparison say Different when the values look identical?
Because they are not identical, even though they look it. Three things cause this, and after a document conversion all three are common.
A trailing space is the usual culprit. "12045 " and "12045" are different strings, and nothing on screen shows the difference. Wrap both sides in TRIM to test it: =IF(TRIM(A2) = TRIM(B2), "Match", "Different").
A number stored as text is the second. If one column holds real numbers and the other holds text that looks like numbers, every single row will report as different. Test with =ISNUMBER(A2) against =ISNUMBER(B2); if one returns TRUE and the other FALSE, that is your answer, and the fix is to convert the text column to numbers before comparing anything.
The third is a non breaking space, character 160, which OCR and PDF extraction both produce and which TRIM does not remove. Strip it with =SUBSTITUTE(A2, CHAR(160), "") and then TRIM the result.
A robust comparison that survives all three looks like this: =IF(TRIM(SUBSTITUTE(A2, CHAR(160), "")) = TRIM(SUBSTITUTE(B2, CHAR(160), "")), "Match", "Different"). It is ugly, and it will save you an hour of staring at two cells that appear to say the same thing.
Comparing amounts, not just text
Money brings its own trap. Two amounts that display as 1,204.50 can differ in the eighth decimal place if one of them is the result of a calculation, and =A2=B2 will report them as different. Compare with a tolerance instead: =IF(ABS(A2 - B2) < 0.01, "Match", "Different") treats anything within a cent as equal, which is what a human reconciling a statement actually means.
When you are matching converted rows against a system of record, run the totals check as well as the row check. Sum both amount columns and compare the two totals. If the rows all match but the totals do not, you have a duplicate or a missing row that a row by row comparison lined up by accident, and the difference between the two sums usually points straight at it.
Comparing two columns in two different sheets
The formulas are identical; only the reference changes. =IF(COUNTIF(Sheet2!$B$2:$B$500, A2) = 0, "Missing", "") compares column A on this sheet against column B on Sheet2. Build the reference by clicking through to the other sheet and dragging over the range rather than typing it, so the sheet name is exactly right, and note that Excel wraps names with spaces in single quotes for you.
Conditional formatting can reference another sheet in modern Excel, but it is fragile and hard to audit. For anything you will keep, a helper column with a visible COUNTIF result beats a formatting rule you cannot see.
Where this fits in a conversion workflow
Comparing two columns is the verification step, and it is the reason converting a document to a spreadsheet beats retyping it. Once the PDF is a real table, you can check it against your records mechanically instead of reading two documents side by side and trusting your eyes.
The check only works when each field landed in its own column. If the conversion dumped everything into column A, there is nothing to compare, and the fix is upstream: use a PDF to Excel converter that reads the table structure, or split the column apart first. And before you compare, remove duplicate rows, because a duplicate on one side will throw off both the counts and the totals. When the two sides finally agree, the file is clean enough to load straight into QuickBooks without a manual review pass.
The short version
Use =IF(A2=B2, "Match", "Different") to compare row by row, and =COUNTIF(B:B, A2) to check whether a value appears anywhere in the other column. Highlight differences with Conditional Formatting, Duplicate Values, switched to Unique. If identical looking values report as different, suspect trailing spaces, numbers stored as text, or a non breaking space, and wrap both sides in TRIM and SUBSTITUTE before you compare. For money, compare with a one cent tolerance rather than an exact equals.