It might be the most common real-world Excel task never taught anywhere: two versions of a list, and someone needs to know what’s different. This month’s customer export versus last month’s. The finance system’s invoices versus the bank’s. Who’s on the new rota but not the old. Eyeballing two thousand rows is not a method — and the actual method is three questions, each one formula.

Setup first, as ever: both lists as proper Tables (ThisMonth, LastMonth), keys cleaned on both sides — a comparison inherits every invisible-space problem a lookup does, so TRIM before you trust anything below.

Question one and two: who’s new, who’s gone?

The membership test is COUNTIF — does this key appear in the other list? Add a column to each Table:

In ThisMonth:  =COUNTIF(LastMonth[ID], [@ID]) = 0     → new
In LastMonth:  =COUNTIF(ThisMonth[ID], [@ID]) = 0     → gone

TRUE in the first column: appeared this month. TRUE in the second: vanished since last month. Two columns, filter to TRUE, done — joiners and leavers, ten seconds after the formulas fill. The modern one-liner does the same as a spilled list:

=FILTER(ThisMonth[ID], COUNTIF(LastMonth[ID], ThisMonth[ID]) = 0)

— “IDs this month that last month never heard of”, self-updating.

last month C-1001 C-1002 C-1003 C-1004 this month C-1001 C-1002 C-1004 C-1005 ← gone new → two COUNTIFs draw every one of these lines — and flag the two that don't exist
Matches pair off; the leftovers are the answer. New, gone — two formulas, and the eyeballing career ends.

Question three: who changed?

The subtle one — present in both lists, but different. For rows that match on key, fetch the old value and compare:

=[@Amount] <> XLOOKUP([@ID], LastMonth[ID], LastMonth[Amount])

TRUE flags every account whose amount moved. Wrap the comparison per-column and you have a change report; the fetched old value beside the new one (IFNA-guarded for the genuinely new rows) gives the before → after your reader actually wants. Comparing many columns at once? Concatenate a fingerprint on each side — =TEXTJOIN("|",, [@Amount], [@Region], [@Status]) — and compare one fingerprint instead of five columns; any difference anywhere trips it. Numbers being compared deserve the ROUND guard: <> on unrounded pennies reports changes nobody made.

For a fast visual pass instead of formulas: conditional formatting’s duplicate/unique rules across both key columns light up the strays — good for a one-off eyeball, no audit trail. And the built-in worth knowing exists: Home → Find & Select → Go To Special has nothing for this, but Inquire’s Compare Files (Professional versions) diffs whole workbooks cell by cell — the heavyweight for “what did they change in v7”.

When it’s monthly, it’s a merge

The formulas are perfect for a one-off. The third month running the same comparison, the usual rule fires: this is a Power Query merge — join the two files on the key with a full outer join, and the query itself labels every row new, gone or both, refreshed each month with no formulas to maintain. Same three questions; the recipe just asks them for you.

New, gone, changed. Every list comparison you’ll ever be handed is those three questions wearing a deadline — and now each one is a formula you can type from memory.