Excel how-to
When XLOOKUP picks the wrong row, it's usually duplicate keys
A repeated ID can make a lookup return a valid value from a different record. Before changing the formula, check whether the key identifies one row in each file. This guide uses a small invoice-line example to show the problem, the checks, and a way to review the exceptions without silently choosing a pair.
A first match is not proof of a unique record.
In the example, INV-1042 appears twice in each export because the invoice has two lines. An exact lookup on Invoice ID alone cannot tell Line 1 from Line 2. With the default first-to-last search, it returns the first matching invoice row, even when you meant the second line.
Source A · invoice lines
source-a.csv
| Invoice ID | Line ID | Amount |
|---|---|---|
| INV-1042 | 1 | 100.00 |
| INV-1042 | 2 | 40.00 |
| INV-1043 | 1 | 75.00 |
| INV-1044 | 1 | 50.00 |
| 1 | 20.00 |
Source B · second export
source-b.csv
| Invoice ID | Line ID | Amount |
|---|---|---|
| INV-1043 | 1 | 75.00 |
| INV-1042 | 1 | 100.00 |
| INV-1042 | 2 | 45.00 |
| INV-1045 | 1 | 60.00 |
| 1 | 20.00 |
Rule: Key: Invoice ID only · Compare: Amount as a number, exact (no tolerance)
- INV-1042
- Duplicate · 2 rows in each source
- INV-1043
- Matched · 75.00
- INV-1044
- Only A
- INV-1045
- Only B
- Empty Invoice ID · Source A
- Invalid key
- Empty Invoice ID · Source B
- Invalid key
Try the lookup in Excel
Import the CSVs into sheets named Source A and Source B, with headers in row 1. On Source A, this formula looks up the invoice in row 3, which is Line 2:
=XLOOKUP(A3,'Source B'!$A$2:$A$6,'Source B'!$C$2:$C$6,"Not found",0)It returns 100.00 from Line 1, not 45.00 from Line 2. Excel followed the requested lookup; the incomplete key picked the wrong business record. Searching from the bottom would select another occurrence, not establish uniqueness.
Formula examples use English function names and comma separators; Excel locale settings may use different names or separators.
These files contain only fictitious data. They are the exports shown above.
Check both files with COUNTIF before trusting a lookup.
On each sheet, count how many times its Invoice ID appears. Put this formula in a spare column on row 2 and fill down. For the repeated INV-1042, it returns 2 in both files.
=COUNTIF($A$2:$A$6,A2)- A count greater than 1 means the proposed key is not unique on that side.
- Check blank IDs separately; an empty key cannot identify the intended record.
- Check both exports. A unique source row can still have several candidates in the other file.
- Investigate repeated rows before deleting them: they may be legitimate invoice lines, payments, or periods.
The same principle applies to employee IDs and customer numbers. A lookup result alone does not tell you whether a second matching row was skipped or a record exists only in the other export.
Use a composite key only when it describes the record.
Here, Invoice ID + Line ID distinguishes the two lines. Check that combination in both files with COUNTIFS; do not add Amount to the key just to make changed values look unique.
=COUNTIFS($A$2:$A$6,A2,$B$2:$B$6,B2)For the non-empty invoice IDs in this example, each combination occurs once on its own side. With that composite key and exact numeric Amount comparison, MatchDock produces:
- INV-1042 + 1
- Matched · 100.00
- INV-1042 + 2
- Difference · 40.00 vs 45.00
- INV-1043 + 1
- Matched · 75.00
- INV-1044 + 1
- Only A
- INV-1045 + 1
- Only B
- Empty Invoice ID · Source A / B
- Invalid key in each source
The Line 2 amount difference is now visible as 40.00 versus 45.00. Empty invoice IDs remain invalid, and a combination that still repeats would remain duplicate or ambiguous. MatchDock does not sum, split, or automatically resolve one-to-many relationships.
Power Query is another option, with the same key decision.
If you already maintain a Power Query process, a merge can compare tables using the columns you select. Check the key's uniqueness and the join type before expanding results: several matching rows can produce more rows than you expected. Choose how to handle those records deliberately rather than assuming the merge means one row matched one row.
Keep using Excel when a formula or query meets your needs. MatchDock is an alternative for reviewing two exports with explicit mappings, rules, and exception groups; it is not a replacement for every Excel task.
Review duplicates explicitly in MatchDock.
01
Download the example files above, then open Start reconciling below and load source-a.csv as Source A and source-b.csv as Source B.
02
Keep header row 1, review the previews, and confirm Invoice ID → Invoice ID, Line ID → Line ID, and Amount → Amount in Mapping.
03
In Match definition, select Invoice ID as a key and Amount as a compare field. Leave Line ID unused for the first run.
04
In Rules, keep Invoice ID exact. Set Amount to Number with 1,234.56 format for both sources and exact comparison (no tolerance). Review, then Run reconciliation.
Expect one matched record, one Only A, one Only B, one duplicate group, and one invalid key in each source: six results. Inspect the duplicate group's rows and explanation. The amounts are not used to invent a pairing. To reproduce the composite-key comparison, start a new run with the same files and select both Invoice ID and Line ID as keys.
Matching is deterministic and one row to one row. You can inspect explanations and export an Excel result workbook locally. With Pro, save the reviewed configuration for future exports; each run still requires your review.
Your spreadsheet contents stay in your browser.
MatchDock parses and reconciles your files locally. Spreadsheet contents are not uploaded to MatchDock servers, and Excel exports are generated in your browser.
Ready to review the exceptions?
Start with two Excel or CSV exports. Choose the records that should match, apply explicit rules, and keep uncertain outcomes visible.