Two versions of the same list land on my desk almost every week. One export from a CRM, one spreadsheet someone has been editing by hand. The question is always the same: what matches, what is missing, and what shows up twice.
Here are the five methods I rotate through, and the point where each one stops being useful.
1. XLOOKUP or VLOOKUP
Best for small lists that already live in Excel. Wrap the lookup in IFNA and you get a clean match flag next to every row.
It breaks quietly, though. Trailing spaces, different capitalization, or one list spelling a name "Muhammad" and the other "Mohammed" will all read as no match, even when a human can see they are the same entry. Past a few thousand rows it also gets slow to audit.
2. Conditional formatting for duplicates
Best for a quick visual check inside one workbook. Select both ranges, highlight duplicates, and the matches light up.
Fast, but the result lives in colors, not data. You cannot hand someone the differences as a list, and the list of differences is usually the actual deliverable.
3. Power Query merge
Best for repeatable comparisons in desktop Excel. Load both lists as queries, merge on the key column, and pick your join type. An inner join gives you the matches, a left anti join gives you what is only in the first list.
It is the most reliable spreadsheet route, but it is desktop only, the first setup takes a while, and it is overkill for a one off check.
4. Sort and diff on the command line
Best for plain text lists, one item per line. Sort both files, and comm shows you the lines only in file one, only in file two, or in both.
Genuinely fast. Also useless for anyone who does not live in a terminal, and it demands perfectly clean input before it tells you anything true.
5. A dedicated online tool
Best for raw lists, one off checks, and anyone who does not want formulas.
When the lists arrive as plain text, in an email or a chat message, converting them into spreadsheet columns just to compare them is wasted effort. I use a simple list comparison tool for these: paste both lists, and it splits the result into matches, items only in the first list, items only in the second list, and duplicates. No formulas to debug, and the output is copyable text I can send straight back.
The rule of thumb
If the lists live in Excel and the check repeats every week, use Power Query. If it is a one off reconciliation between two messy lists, use the online tool. Everything else sits somewhere in between.
The method matters less than cleaning the input first. Trim the extra spaces and decide whether capitalization should count, or every method above will lie to you in exactly the same way.
Top comments (0)