Two reports can contain the same invoice IDs, customer codes, employee numbers, or stock SKUs—and still disagree in ways that are easy to miss. One file may omit a record, repeat an ID, or show a payment amount that differs by a few cents.
The reliable way to reconcile two Excel lists is to build one master ID list, count each ID in both source lists, pull the comparable values with XLOOKUP, and assign a clear status. This works well for an invoice register versus a payment report, expected versus actual inventory, or CRM versus ERP exports.
Quick Solution
Recommended: turn both exports into Excel Tables, then create a separate reconciliation table containing every ID from both files.
- Name the source tables
tblInvoicesandtblPayments. - Create a master
Invoice IDcolumn by appending IDs from both lists, then use Data > Remove Duplicates. - Use
COUNTIFto identify duplicate or missing IDs before usingXLOOKUP. - Pull the amount and date from each list, then use one status formula to label each exception.
- Apply conditional formatting to make every status except Matched visible immediately.
This order matters. XLOOKUP returns the first match it finds, so it cannot by itself tell you that an invoice ID appears twice.
Set Up a Repeatable Reconciliation Sheet
Start with two clean source lists. Click anywhere in each export and press Ctrl+T to create an Excel Table. Confirm that My table has headers is selected, then rename the tables from Table Design.
Example source data
In this example, the invoice register is the expected record and the payment report is the actual record.
| tblInvoices: Invoice ID | Amount | Invoice Date |
|---|---|---|
| INV-1001 | 1,250.00 | 1/5/2025 |
| INV-1002 | 800.00 | 1/7/2025 |
| INV-1003 | 450.00 | 1/9/2025 |
| INV-1004 | 600.00 | 1/10/2025 |
| tblPayments: Invoice ID | Amount | Payment Date |
|---|---|---|
| INV-1001 | 1,250.00 | 1/5/2025 |
| INV-1002 | 750.00 | 1/7/2025 |
| INV-1003 | 450.00 | 1/11/2025 |
| INV-1005 | 300.00 | 1/12/2025 |
Create a third table named tblReconcile. Its first column is Invoice ID. Copy the ID columns from both source tables underneath each other, then select that combined column and choose Data > Remove Duplicates. This master list ensures you catch IDs missing from either report.
If you use Microsoft 365 and only need a quick, non-table list, enter this in an empty cell such as A2:
=SORT(UNIQUE(VSTACK(FILTER(tblInvoices[Invoice ID],tblInvoices[Invoice ID]<>""),FILTER(tblPayments[Invoice ID],tblPayments[Invoice ID]<>""))))The formula spills a sorted list of unique nonblank IDs. For a maintained reconciliation workbook, the copied master list in tblReconcile is often easier to review and annotate.
Add Counts, Lookups, and a Status Column
Add these columns to tblReconcile: Invoice Count, Payment Count, Expected Amount, Paid Amount, Expected Date, Paid Date, and Status.
Step 1: Count each ID before looking it up
In tblReconcile[Invoice Count], enter:
=COUNTIF(tblInvoices[Invoice ID],[@[Invoice ID]])In tblReconcile[Payment Count], enter:
=COUNTIF(tblPayments[Invoice ID],[@[Invoice ID]])A count of 0 means the ID is absent. A count above 1 means it is duplicated and needs investigation before a one-to-one comparison is trustworthy.
Step 2: Return the values to compare
In Expected Amount, enter:
=IFNA(XLOOKUP([@[Invoice ID]],tblInvoices[Invoice ID],tblInvoices[Amount]),"")In Paid Amount, enter:
=IFNA(XLOOKUP([@[Invoice ID]],tblPayments[Invoice ID],tblPayments[Amount]),"")Use the same pattern for the date columns, changing the return column:
=IFNA(XLOOKUP([@[Invoice ID]],tblInvoices[Invoice ID],tblInvoices[Invoice Date]),"")=IFNA(XLOOKUP([@[Invoice ID]],tblPayments[Invoice ID],tblPayments[Payment Date]),"")Important: IFNA only replaces a genuine “not found” result. That is safer than using IFERROR, which can hide an unrelated formula problem.
Need a deeper explanation of exact-match lookups and return arrays? See our Excel XLOOKUP complete guide.
Step 3: Assign an auditable status
In tblReconcile[Status], enter this formula:
=IF([@[Invoice Count]]>1,"Duplicate invoice ID",IF([@[Payment Count]]>1,"Duplicate payment ID",IF([@[Invoice Count]]=0,"Missing from invoice register",IF([@[Payment Count]]=0,"Missing from payment report",IF(AND([@[Expected Amount]]=[@[Paid Amount]],[@[Expected Date]]=[@[Paid Date]]),"Matched",IF(AND([@[Expected Amount]]<>[@[Paid Amount]],[@[Expected Date]]<>[@[Paid Date]]),"Amount and date mismatch",IF([@[Expected Amount]]<>[@[Paid Amount]],"Amount mismatch","Date mismatch"))))))The formula checks problems in the right order:
- Duplicates first: stop a first-match lookup from being treated as a valid reconciliation.
- Missing records next: label which report lacks the ID.
- Value comparison last: compare amounts and dates only when one record exists in each list.
| Invoice ID | Expected Amount | Paid Amount | Status |
|---|---|---|---|
| INV-1001 | 1,250.00 | 1,250.00 | Matched |
| INV-1002 | 800.00 | 750.00 | Amount mismatch |
| INV-1003 | 450.00 | 450.00 | Date mismatch |
| INV-1004 | 600.00 | Blank | Missing from payment report |
| INV-1005 | Blank | 300.00 | Missing from invoice register |
Highlight the Exceptions with Conditional Formatting
Filtering the Status column to exclude Matched creates an instant exception report. Conditional formatting makes the same result easier to scan.
- Select the data rows in
tblReconcile. - Choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format.
- If
Statusis column H and the first data row is 2, use:
=$H2<>"Matched"Choose a light fill color. The dollar sign locks the status column while Excel evaluates each row. You can add separate text-based rules for duplicates, missing records, and mismatches if reviewers need different colors. For more rule-management tips, see the Excel conditional formatting guide.
Power Query for Monthly or Recurring Reconciliations
Formulas are ideal when you need a review sheet with visible calculations. If the same two exports arrive every week or month, Power Query can make the import and comparison repeatable.
- Load each Excel Table using Data > From Table/Range.
- In each query, set the ID column to the same data type and rename differing headers to a common name where useful.
- Group each query by ID and count rows first. This exposes duplicates before the merge.
- Use Home > Merge Queries, select the ID fields, and choose a Full Outer join to retain IDs from both sources.
- Expand the amounts and dates, add a custom status column, then use Close & Load to return the exception table to Excel.
Caution: merging raw lists that contain duplicate IDs can create several paired rows. Checking or grouping duplicate keys before the merge prevents a misleading result. For the broader import workflow, read Power Query Pipeline: CSV to Model to Report.
Where AI Fits—and Where It Does Not
AI can be useful before the reconciliation begins. For example, provide anonymized headers such as Cust No., Client_ID, and Account Code, then ask which fields may represent the same business key. It can also help explain a redacted exception summary: “Group these mismatch reasons into likely process issues.”
Do not let an AI explanation replace the actual comparison. Keep the Excel status column or Power Query output as the final evidence, and avoid uploading raw financial, customer, employee, or payment data to tools that are not approved for it. Our Excel + AI data safety guide covers practical precautions.
Reconciliation Troubleshooting
| Problem | Likely cause | Solution |
|---|---|---|
| Known ID shows as missing | One ID is text and the other is numeric, or a hidden space exists. | Standardize the ID format. Use a helper cleanup column such as =TRIM(A2); avoid removing meaningful leading zeros. |
| Dates look identical but mismatch | One value includes a time, or one “date” is text. | Check the formula bar. Convert text dates properly; use INT() only when the time portion should be ignored. |
| Amounts differ by 0.01 | Different rounding rules or source precision. | Confirm the business rule before comparing rounded amounts with ROUND. |
| All records return the first value | Duplicate IDs exist in a source. | Review the count columns first. Do not accept the first XLOOKUP result as the answer. |
#SPILL! appears |
Cells block the dynamic-array master ID formula. | Clear the intended spill range, or use the copied master-list method. |
Final Reconciliation Checklist
- Both source ranges are Excel Tables with clear headers.
- The master ID list contains IDs from both reports.
- Duplicate counts are checked before lookup results.
- Amounts and dates use consistent data types and comparison rules.
- The status column distinguishes missing, duplicate, and mismatch exceptions.
- Only exceptions are sent for review; matched records remain available for audit.
FAQ
Can I reconcile two lists with VLOOKUP?
Yes, but XLOOKUP is generally easier because it can return values from columns on either side of the lookup column. In either case, use COUNTIF to detect duplicate IDs.
What if one invoice can have several payments?
Do not use a one-to-one lookup. Summarize payments by invoice ID first, such as with a PivotTable, SUMIFS, or Power Query Group By, then compare the invoice total with the payment total.
Should I use exact dates in a reconciliation?
Only if the two systems are expected to record the same date. If payment date naturally differs from invoice date, compare the relevant dates instead, such as due date versus settlement date, or remove the date test from the status formula.