Upload a workbook and pick two columns, on one sheet or two, or paste two lists. See which values are in both, which are only in the first and which only in the second, then copy any list or download all three as an Excel file.
Nothing is uploaded. The file or lists are compared in your browser and never sent anywhere.
This tool lets you compare two columns in Excel by value, in your browser: every value in one column is looked for anywhere in the other, so the two can be sorted differently and run to different lengths. The usual reason is a reconciliation. The ledger has 2,418 invoice numbers, the bank's remittance export has 2,395, and you want two short lists out of it: the invoices nobody has paid, and the payments that don't match any invoice.
<your file> column comparison (xlsx.com).xlsx. Its Results sheet puts the three lists side by side with the counts in the headings. The Rows sheet lists every value of each list in its original order, next to its source row number, with a Status of Match, Only in A or Only in B in green or red.Nothing is uploaded, and your own file is left untouched. It stays open while you try other columns, so a second comparison doesn't read it again.
Results are distinct values: an invoice number that appears three times in List A shows once, and the summary says how many repeats were counted once. Blank cells are skipped.
The tool compares what a cell holds, not how it's formatted. 1200 formatted as $1,200.00 matches a plain 1200, and two real dates match even when one column shows 5 Jan 2026 and the other 2026-01-05. A formula cell compares by the result saved in the file, and numbers compare to 15 significant digits as Excel does, so a cell holding 0.1+0.2 matches 0.3.
The awkward cases are where Excel's own functions disagree with each other:
| List A | List B | This tool | COUNTIF | XMATCH |
|---|---|---|---|---|
| East | EAST | Match | Match | Match |
| 123 (a number) | 123 (text) | Match | Match | No match |
| 00123 (text) | 123 (a number) | No match | Match | No match |
| 4000123412341234 (text) | 4000123412341235 (text) | No match | Match | No match |
A*1 | A-1 | No match | Match | No match |
| "Smith " with a trailing space | Smith | Match with "Trim spaces first" on | No match | No match |
The second row is behind a long-running forum question: why does COUNTIF find a value that VLOOKUP says isn't there? Product codes typed into a column formatted as Text, or pasted from an ERP report, are stored as text, while the price list they're checked against holds numbers. COUNTIF finds every code, and a VLOOKUP over the same two columns returns #N/A on every row. When the tool pairs a number with text, the result says how many matches did so and that XMATCH, MATCH and VLOOKUP would treat them as different values.
The tool keeps codes with leading zeros as text, so 00123 doesn't match 123. Numeric text longer than 15 significant digits, like card numbers and long order numbers, also stays text, so two IDs that differ only in the last digit never match. COUNTIF counts both of those as matches.
The A*1 row is COUNTIF reading the asterisk as a wildcard. It does the same with ?, reads ~ as an escape character (so a value containing a tilde can fail to find itself), and a value starting with <, > or = becomes a comparison. MATCH with 0 and VLOOKUP with FALSE also treat * and ? as wildcards; XMATCH only does in wildcard mode.
When two values look identical and still don't match, suspect a space: a trailing one, or a non-breaking space picked up from a web page.
"Trim spaces first" works like Excel's TRIM: nothing at either end, and a run of spaces between words cut to one. It also treats a non-breaking space as a space, which TRIM doesn't. With trimming off, the result tells you when it would add matches ("1 more value would match with spaces trimmed"), and a button turns it on and compares again.
In Excel, clear both kinds before comparing (CHAR(160) is the non-breaking space):
=TRIM(SUBSTITUTE(A2,CHAR(160)," "))Most guides start with =A2=B2 filled down, which asks whether row 5 of A equals row 5 of B, which is the right question for two versions of the same table in the same order, such as last month's prices against this month's. The tool answers a different question: is this value anywhere in the other column? Sort order doesn't matter, and the lists can be different lengths.
For a case-sensitive row check, use =EXACT(A2,B2). Without a formula, select both columns, press F5, click Special and choose Row differences. Excel selects every cell that differs from the one beside it in the first column.
Under the results, the tool shows the same check as a copyable formula built from the sheet and column you picked. With List A in column B and List B on a sheet called Last year, the version for any Excel is:
=IF(COUNTIF('Last year'!$B$2:$B$6,B2)>0,"Match","Only in A")On Microsoft 365 or Excel 2021 and later:
=IF(ISNUMBER(XMATCH(B2,'Last year'!$B$2:$B$6)),"Match","Only in A")The single quotes are there because the sheet name has a space. Type the formula in the first data row beside List A, fill it down, then filter the new column for Only in A. Swap the two references to flag List B instead.
The formula follows your options. With trimming on, COUNTIF gives way to SUMPRODUCT(--(TRIM(range)=TRIM(cell)))>0, since COUNTIF won't take TRIM of a range; with case matching on, both versions switch to EXACT. When they apply, notes under the formula flag where it and the tool can disagree, such as TRIM and EXACT comparing as text, so 1.5 and the text 1.50 don't match in the formula.
The formula is the better choice when the lists change every week. A flag column in the sheet updates itself; the tool has to be run again. To colour the gaps instead, select List A from its first value down (A2:A500 here) and add a conditional formatting rule (Home > Conditional Formatting > New Rule) with =COUNTIF($D$2:$D$500,A2)=0. The A2 in the rule has to be the first cell of the selection. The Duplicate Values rule also colours repeats within one column, which muddles the picture.
Each function in these formulas has its own page: COUNTIF, MATCH and EXACT.
=A2&" "&B2) and compare those.If you need a value from the matching row, not a Match flag, the XLOOKUP generator builds that formula. For counting matches that also meet other conditions, the COUNTIF generator writes the COUNTIFS.
Fill a COUNTIF flag down next to the first list: =IF(COUNTIF($D$2:$D$500,A2)=0,"Missing","") marks every value in column A that appears nowhere in D2:D500. Swap the two references to check the other direction. In Microsoft 365, =FILTER(A2:A500,ISNA(XMATCH(A2:A500,D2:D500))) lists the missing values in one formula.
Usually one of them has a space you can't see, or one is a number and the other is the same number stored as text. TRIM removes ordinary spaces but leaves the non-breaking spaces that come with text copied from web pages, so use TRIM(SUBSTITUTE(A2,CHAR(160)," ")) for those. MATCH, XMATCH and VLOOKUP also treat the number 123 and the text 123 as different values, while COUNTIF counts them as the same.
No. The = sign, COUNTIF, MATCH, XMATCH and VLOOKUP all treat East and EAST as equal. For a case-sensitive check use EXACT: =EXACT(A2,B2) for two cells on the same row, or =SUMPRODUCT(--EXACT($D$2:$D$500,A2))>0 to look for A2 anywhere in a list.
Put the sheet name in front of the other list's range: =IF(COUNTIF('Last year'!$B$2:$B$500,B2)>0,"Match","Only in A"). A sheet name with a space in it needs the single quotes. The formula sits on the first list's sheet and is filled down beside it.
Use a conditional formatting rule based on COUNTIF. Select the first list from A2 down (A2:A500, with A2 the active cell), choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format, enter =COUNTIF($D$2:$D$500,A2)=0 and pick a fill. Every value that isn't in the other list turns that colour.
Row by row asks whether A5 equals B5, which is =A2=B2 filled down. A list comparison asks whether the value in A5 appears anywhere in column B, so the sort order and the lengths of the two columns don't matter. Two customer lists sorted differently need the list comparison.