The rows are rarely the problem. Excel usually fails because the two lists are using different keys, different data types, or a different idea of what counts as the same record.

That is why conditional formatting can light up half the sheet and still leave you with no answer. If you need to compare two Excel lists and find discrepancies, start by deciding whether you are checking for missing items, mismatched values, or mismatched records. Those are different jobs, and Excel gives bad results when they are forced into one formula.

Before you build anything, identify what kind of discrepancy you are actually looking at.

What the discrepancy looks like before you touch the workbook

If you are not sure which case you have, run =TYPE(A2) on a suspect cell in each list. TYPE() returns 1 for numbers and 2 for text. That tells you immediately whether the discrepancy is a real content difference or a storage difference that Excel is turning into a false mismatch.

Decide what kind of comparison you are actually doing

Most search results for this topic assume the problem is one clean column against another clean column. Sometimes that is true. Often it is not.

That distinction matters because row position is almost never the right comparison key. If one list is sorted by date and the other is sorted by customer, row 84 in one sheet has no reason to equal row 84 in the other. A row-by-row formula is not finding discrepancies there. It is manufacturing them.

If both lists are genuinely simple, use the short formula and stop there

If each list is one clean column and your only question is, "What exists in A but not in B?" then the fast answer is enough.

For modern Excel:

=FILTER(A2:A5000,COUNTIF($D$2:$D$5000,A2:A5000)=0)

That returns values from List A that are missing from List B.

To do the reverse:

=FILTER(D2:D5000,COUNTIF($A$2:$A$5000,D2:D5000)=0)

For older Excel, use a helper result column:

=IF(COUNTIF($D$2:$D$5000,A2)=0,A2,"")

That is enough when:

• each list has one comparison column
• duplicates do not matter
• row order does not matter
• you only care about presence or absence

The moment the same record spans several columns, or the same value can appear more than once, that simple formula stops answering the full question. If your job has already become "which exported rows are missing or unmatched across two files," finding missing rows between two CSV files in Excel is the closer problem pattern.

Choose a match key before you compare values

A discrepancy report is only as good as the key used to line the records up.

If one field is not unique enough, build a composite key. A common pattern is reference plus date plus amount:

=A2&"|"&TEXT(C2,"yyyy-mm-dd")&"|"&TEXT(D2,"0.00")

That works because each part is doing a different job:

• the reference identifies the transaction
• the date separates reused references across periods
• the amount separates split or partial entries

Once both lists have the same key, you can compare the rest of the record against it. For example, if column H is your composite key in both sheets:

=XLOOKUP($H2,Sheet2!$H$2:$H$5000,Sheet2!$J$2:$J$5000,"Missing")

That pulls the field from List B that belongs to the same key in List A. You can then test the returned value against the original field instead of comparing row positions.

Check the key for duplicates before you trust the output:

=COUNTIF($H$2:$H$5000,H2)

If that returns anything above 1, you do not have a clean one-to-one key yet. XLOOKUP will return the first match and hide the rest. That is not a formula success. It is a comparison failure dressed as a result.

Normalize the values that look equal but are stored differently

This is where most spreadsheet comparison guides stop too early. They show the match formula, but not the data cleanup that the formula depends on.

Text on one side, number on the other

If List A stores 1245 as text and List B stores it as a number, confirm it with TYPE() first:

=TYPE(A2)

If A returns 2 and the matching cell in List B returns 1, convert inside the lookup before you build a helper column.

If the value should be numeric:

=XLOOKUP(VALUE(A2),Sheet2!$F$2:$F$5000,Sheet2!$G$2:$G$5000,"Missing")

If the value should remain fixed-width text with leading zeros, force both sides to the same text format:

=XLOOKUP(TEXT(A2,"000000"),TEXT(Sheet2!$F$2:$F$5000,"000000"),Sheet2!$G$2:$G$5000,"Missing")

The important part is not the exact mask. It is that both sides are being compared as the same type.

Hidden spaces and CHAR(160)

CSV exports and copied web data often contain a non-breaking space, which Excel represents as CHAR(160). It looks like a normal space on screen, but TRIM() alone will not always remove it.

Normalize it inside the comparison:

=XLOOKUP(TRIM(SUBSTITUTE(A2,CHAR(160)," ")),TRIM(SUBSTITUTE(Sheet2!$F$2:$F$5000,CHAR(160)," ")),Sheet2!$G$2:$G$5000,"Missing")

That removes leading and trailing spaces and replaces the hidden CHAR(160) with a normal space first.

Text dates that are ambiguous

If one list stores dates as text, do not jump straight to DATEVALUE(). A text date like 01/05/2026 is ambiguous. One system may mean January 5. Another may mean May 1.

For text stored as dd/mm/yyyy, extract it explicitly:

=DATE(RIGHT(A2,4),MID(A2,4,2),LEFT(A2,2))

For text stored as mm/dd/yyyy, use:

=DATE(RIGHT(A2,4),LEFT(A2,2),MID(A2,4,2))

Once the text date is converted into a real Excel date, compare it as a date or standardize it inside a key with TEXT(real_date,"yyyy-mm-dd").

The ambiguity matters because a formula can convert the text successfully and still give you the wrong answer if it assumes the wrong date order.

Compare full records, not isolated cells

Once the match key is stable and the data types are normalized, compare the actual record fields one by one.

Suppose List A contains:

• column A: transaction ID
• column B: date
• column C: amount
• column D: status

And List B contains the same fields in different column positions.

The workflow is:

1. Match List A to List B by the stable key.
2. Pull back the corresponding value from List B.
3. Classify the result as matched, different, or missing.

For example, if column K in List A returns the amount from List B:

=XLOOKUP($H2,Sheet2!$H$2:$H$5000,Sheet2!$C$2:$C$5000,"Missing")

Then classify the outcome:

=IF(K2="Missing","Missing from List B",IF(C2<>K2,"Amount difference","Matched"))

Do the same for date, status, or any other field that matters.

This gives you a useful discrepancy report because it separates three very different situations:

• the record exists in both lists and agrees
• the record exists in both lists but a field differs
• the record does not exist on the other side at all

That is the level where the output becomes actionable.

Classify the discrepancies instead of painting the sheet

A good comparison does not end with highlighted cells. It ends with named exception types.

This is also the point where conditional formatting should move into a supporting role. It is useful for visual review after the discrepancy classes are already defined. It is not the method that defines them.

If your lists are really transaction exports with shifted headers, different column names, and date or sign mismatches, comparing two bank statement CSVs without formulas is the same problem at the file level rather than the single-sheet level.

When Excel is creating the work instead of reducing it

Excel is still fine for small comparisons. It starts turning into overhead when the routine looks like this every time:

• import two new lists
• recheck the key
• normalize dates again
• strip hidden spaces again
• test for duplicates again
• repair the comparison because one export changed shape

At that point, the problem is not that you do not know how to compare lists. The problem is that the lists keep arriving in forms that need repair before the comparison can even start.

That is why so many "Excel compare two lists differences" workflows feel unstable. The formula is rarely the hard part. The unstable part is the repeated file cleanup, key selection, and exception sorting around the formula.