Reconcile Invoice Payments in n8n with Forms & XLSX Merge

Reconcile Open Invoices vs. Bank Statements in n8n (with Forms + XLSX Merge)

This workflow lets you upload an Open Invoices .xlsx and a Bank Statement .xlsx via an n8n Form, then automatically matches bank credits to invoices and returns a styled HTML reconciliation report—showing exact matches, partial discrepancies, unpaid invoices, and unmatched deposits.

What this workflow does

• Collects files: Receives two uploaded .xlsx files through an n8n Form submission—one for Open Invoices and one for the Bank Statement.
• Extracts spreadsheet rows: Uses “Extract from file” to convert both spreadsheets into structured items for processing.
• Matches credits to invoice IDs: Compares each bank credit transaction to invoices by checking whether the bank Reference / Description contains the full invoice ID or the last three digits.
• Validates amounts: Evaluates whether the credited amount matches the invoice Total Amount Due.
• Classifies results: Groups outcomes into:
  • Exact matches
  • Partial matches (amount discrepancies)
  • Unpaid invoices
  • Unmatched deposits
• Generates an HTML dashboard: Renders a printable, styled reconciliation report and shows it as the form completion response, including summary metrics like totals collected and match rate.

Use cases

• Finance teams reconciling customer payments against an Open Invoices export.
• SaaS operators reducing manual matching of bank credits to invoice references.
• Automation engineers standardizing invoice payment reconciliation using n8n.

Technical details

• n8n nodes: Code, Form, Form Trigger, Extract from file, Merge, and supporting workflow elements (e.g., Sticky Note).
• Expected spreadsheet columns:
  • Open Invoices: Invoice ID, Total Amount Due
  • Bank Statement: Credit, Debit, Value Date, Reference / Description
• If your headers or invoice/reference formats differ, you can update the matching code logic to fit your exports.