Compare Two Lists (Find Missing)
excel-compare• Compare two lists to find items that are only in one of them. • Pick items missing from the reference or the common ones, export to Excel (xlsx).
The “reference” we check membership against.
Finds items here that are not in the reference.
When comparing two columns helps
Sometimes you are handed two lists and need to know who dropped off and who is new. If the sort order differs or there are many rows, checking by eye is exactly where mistakes creep in.
In Excel this is usually done with COUNTIF: if =COUNTIF(B:B, A2) returns 0, the value in A2 is not present in column B. Checking both directions means building the formula twice, which is tedious. Here you paste both lists once and see the result from both sides together.
Cleaning up spaces and letter case before comparing matters. Two values can look identical while one carries a trailing space, and they will be treated as different. Pasted data is processed only in your browser and never sent to a server.
What to check when the comparison looks wrong
Reconciling two lists usually goes wrong because of how values are written, not because the values differ. Job titles appended to names, a company written out in full in one file and abbreviated in the other, inconsistent spacing in addresses. Normalising whatever you can on both sides first makes the comparison far easier.
Be careful with values that only look numeric. Employee numbers, student IDs and registration numbers lose their leading zero when the cell format is numeric, and long ones can flip to scientific notation. Compare a sheet stored as text against one stored as numbers and identical values get reported as missing on both sides.
Checking the counts first catches the big mistakes immediately. Items only in A plus items in both should equal the total in A. If it does not, one of the lists probably contains duplicates; remove those first, then compare again.
Decide in advance what you will do with each result. Items only in A are usually the ones missing, unpaid or unsent, while items only in B arrived without being on the list at all. They call for different follow-up, so merging them into one list just means separating them again later.
Frequently asked questions
How do I compare two lists and pull out only the missing people?
Paste the reference list on the left and the comparison list on the right, one value per line. In comparison only shows what is new, In reference only shows what dropped out, and In both shows the overlap, so one click switches between all three views.
VLOOKUP returns #N/A when I compare. Does that mean the value is missing?
It means the value was not found in the range. The problem is that a genuine miss and a failure caused by a stray space look identical. Counting instead, as in =IF(COUNTIF(B:B,A2)=0,"missing","found"), reads better, and here the result comes back as a plain list.
Do the two lists need the same sort order or the same number of rows?
Neither. Values are matched on their content, so nothing has to be sorted first and the two lists can be different lengths. Turn on Sort result if you want the output in alphabetical and numeric order.
What happens if the same value appears several times in one list?
Repeats inside a list are collapsed into one before comparing. The counts in the summary are therefore unique counts, and no value appears twice in the result. If you want to examine the duplicates within a single list, the remove duplicates tool is the right one.
How do I treat email addresses as equal regardless of letter case?
Leave Case sensitive off and Hong and hong are treated as the same value. It is off by default, so this works out of the box. Turn it on for coupon codes or serial numbers, where upper and lower case actually carry meaning.
How do I move the result back into Excel?
Copy puts the values on the clipboard one per line, ready to paste into a column, and Excel (xlsx) downloads them as a single-column sheet. The screen shows up to 300 items, but the copy and the download always contain every result.