Compare two CSV lists without losing exact identifier matches

Find absent identifiers, duplicate reference keys and case differences with a two-way Excel comparison and downloadable test cases.

Two exports both contain eight rows. That does not mean they contain the same records. Before you join a reference list to an order list, count exact matches for each identifier. A count of zero needs investigation; a count greater than one means a lookup could return an arbitrary reference row.

This guide checks whether each key exists and whether its match is unique. It does not delete duplicates, compare amounts, or prove that two entire records are equal. Use it after importing identifiers safely and before attaching reference information or handing off an export.

Decide which question you are answering

This procedure checks whether an identifier exists in the other list. It does not prove that the records have the same amounts or descriptions, or that a duplicate key maps one-to-one. Import both identifier columns as Text before putting them into the comparison worksheet, and verify a leading-zero value after copying. To compare the fields of uniquely keyed records, use the separate field-change guide, which requires Python.

Set up the two lists

Download the left list, the reference list, the left-side expected results and the reverse expected results. Each input has eight data rows and two columns: row_id and record_id. row_id is a traceability label; record_id is the key to compare.

Keep the source files unchanged. Import both record_id columns as Text using the identifier import steps. Place the left file including its header in A1:B9, and the reference file in F1:G9 of a working sheet. Leave E as a spacer. Keep row 8 on both sides even though its record_id is blank. Do not remove that row or replace the blank with the word blank.

For a formula-only exercise, format B2:B9 and G2:G9 as Text before entering the input values from the downloaded files. Format formula columns C:D and H:I as General. Confirm B2 still contains 00127 and B3 contains 0127. If zeros were lost, reload the original; padding every code to the same length changes this exercise's keys.

Choose the matching rule

Our contract is case-sensitive, literal text equality. 00127, 0127 and 127 are three different keys. AbC-7 differs from abc-7. An asterisk in A*1 is part of the identifier, not a wildcard matching AB1. Leading or trailing spaces also count as differences.

Agree on these rules with the owner of the data. If a business system treats case or spaces differently, normalize into a separate column under that explicit rule and retain the raw values. The space-cleaning guide explains why blanket trimming can change meaning. Do not silently change the rule just to make more rows match.

Count exact matches before looking up values

Enter reference_count in C1 and status in D1. Formula blocks scroll horizontally on narrow screens; copy the complete formula. In C2 enter:

=IF(B2="","",SUMPRODUCT(--EXACT(B2,$G$2:$G$9)))

EXACT compares the current key against each of the eight reference cells. The double minus converts TRUE and FALSE results to 1 and 0, and SUMPRODUCT adds those matches. Dollar signs keep the reference range fixed when the formula fills down. The outer IF withholds a count for a missing key, so a blank on each side is not reported as a valid match.

In D2 enter:

=IF(B2="","missing key",IF(C2=0,"not found",IF(C2=1,"one match","duplicate in reference")))

Select C2:D9 and use Fill Down. Check that the last row refers to B9 and C9, while its reference range remains $G$2:$G$9. Use semicolons between arguments if your Excel installation uses that list separator.

Check the eight left-side results

The expected totals are two one-match rows, four not-found rows, one duplicate-reference row and one missing-key row. All eight left-side records remain. These are row counts, not counts of distinct keys or a proof that complete records match.

If DUP returns only one, inspect the reference range and ensure both R05 and R06 are included. If A*1 matches AB1, you are using a different comparison rule. If every result is zero, inspect source types and invisible spaces before changing the formula.

Check in the other direction

A left-only check cannot identify extra rows in the reference list. Add left_count in H1 and status in I1. In H2 enter:

=IF(G2="","",SUMPRODUCT(--EXACT(G2,$B$2:$B$9)))

In I2 enter:

=IF(G2="","missing key",IF(H2=0,"not found",IF(H2=1,"one match","duplicate in reference")))

Fill H2:I9 down. In this direction, reference means the left range being searched. R03, R04 and R08 have no exact left-side match; R07 has a missing key. Four reference rows have one match. R05 and R06 both match L06, which demonstrates why existence in both directions is not a one-to-one reconciliation. The earlier count of two for L06 still needs resolution.

For longer lists, extend the absolute range to the actual last row on the opposite side and fill through every source record. Use bounded ranges rather than entire columns. This exercise is intended for small office lists; repeated array comparisons across very large exports can be slow.

Resolve exceptions without hiding them

Investigate not-found keys with the source owner. Separate a genuinely absent record from a spelling, case, whitespace or import problem. For duplicate keys, decide whether the reference needs a compound business key or contains accidental duplicates; see duplicate removal with a clear key. Do not keep the first match merely because a lookup function returns it.

Retain the original row labels and the status columns when sharing the result. Before sending a CSV, use the export check to confirm the exact identifiers survive. Comparing keys does not compare the other columns: a matched ID can still carry a different date, amount or description.

Actual test record

On October 6, 2026, both eight-row inputs were written as literal text into a separate connected Excel for Mac 16.113.2 workbook. Excel calculated all eight left-side and eight reverse results; the returned values and formulas matched the downloadable expectations. The test included distinct zero-prefixed IDs, case differences, a literal asterisk, repeated reference keys and blank keys.

Changing G4 from abc-7 to AbC-7 changed L04 from zero/not found to one/one match. Restoring G4 returned the original result. This verified recalculation rather than just reading a saved answer.

The scope is native worksheet formulas after text-preserving input, not a new CSV-import path. The Excel interface was Chinese; this page uses English instructions and reference tables. Windows and English-interface operation were not tested. Independent file checks compare the exact strings. Download the dated test notes.

References

Microsoft documents the case-sensitive behavior of EXACT and the array calculations of SUMPRODUCT. The matching contract, data and reconciliation checks here are original practice examples.