To reconcile two lists, match records using a stable identifier and then compare the fields that matter. Do not compare row 2 with row 2 unless the relationship has been established: two correct lists can arrive in different orders.

Keep both source lists intact. Put the checks and exceptions beside them or on a separate review sheet.

Source IDs S001, S002 and S003 are compared with received IDs S002, S003 and S004, exposing one missing ID, one unexpected ID and one changed amount.

Compare records by ID, not row position. Original instructional illustration; the examples are not software screenshots.

Use a small example

Source ID

Amount

Received ID

Amount

S001

90

S002

120

S002

120

S003

75

S003

80

S004

100

S002 matches. S001 is missing from the received list, S004 is unexpected and S003 has a changed amount. A single “lists differ” result would hide those separate problems.

Check that the identifier is unique

In the example layout, put source IDs in A2:A4, amounts in B2:B4, received IDs in D2:D4 and amounts in E2:E4. A helper formula =COUNTIF($A$2:$A$4,A2) counts occurrences of the source key. A unique source key should give 1.

If duplicates are legitimate, define a more specific record key, such as order ID plus line ID. Do not delete duplicate-looking rows until you understand what each represents.

Look for missing records in both directions

Alongside each source ID, use =COUNTIF($D$2:$D$4,A2). Zero means the ID has no match in the received range; more than one needs a duplicate review. Reverse the check with =COUNTIF($A$2:$A$4,D2) beside received IDs.

COUNTIF counts entries meeting a criterion. It also supports wildcard characters, so these simple literal-key examples are intended for IDs such as S001 without wildcard characters. For case-sensitive identifiers, use a matching method that explicitly preserves that distinction. Microsoft's COUNTIF guide.

A reconciliation workflow checks unique keys and bidirectional presence before comparing matched amounts and recording exceptions.

A matching ID is only the first check. Original instructional illustration; the examples are not software screenshots.

Compare the matching values

For a small list, locate each matched key and compare its amount directly. S003 differs by 5 in this example. For larger work, use a verified key-based matching workflow and keep the exceptions visible.

Check units, reporting periods and any agreed rounding tolerance. Matching totals can conceal offsetting mistakes, so use them alongside record-level checks.

Save an exception log with the ID, issue, owner and resolution. After corrections, rerun both presence checks and the value comparison. Ulet lets you keep these local XLSX review files together with free basic spreadsheet editing.