How to match CRM deals to invoices
By the Analistable team · Updated · 6 min read
Export closed-won deals from the CRM and invoices from your billing or accounting tool. Match on a deal ID stored on the invoice if you have one; otherwise on customer + amount within a date window. Closed-won deals with no invoice are revenue leakage; invoices with no deal show sales the CRM never recorded.
Part of our guide: How to join spreadsheets on a common column
Prepare the key
The cleanest setup stores the CRM deal ID on the invoice (a custom field or the invoice reference). Without it, build a key from the customer's name or domain and the amount, and match within 30 days of the close date.
Key =LOWER(TRIM([@Account])) & "|" & TEXT([@Amount], "0.00")
Invoiced =COUNTIFS(Invoices[Key], [@Key], Invoices[Date], ">=" & [@[Close date]], Invoices[Date], "<=" & [@[Close date]] + 30) > 0Worked example
| Deal | Account | Amount | Close date | Invoice found | Action |
|---|---|---|---|---|---|
| D-551 | Bakery Lune | 4800 | 2026-07-14 | INV-301 | — |
| D-560 | Hart & Co | 12000 | 2026-08-02 | No | Raise invoice |
| D-571 | Nordic Supply | 3500 | 2026-09-20 | INV-322 (3,150) | Amount differs: discount not in CRM |
The reverse check — invoices with no deal — uses the same key from the invoice side: =COUNTIFS(Deals[Key], [@Key]) = 0.
What the gaps usually mean
- Deal closed but invoice not raised: the handover from sales to finance failed.
- Invoice amount below deal amount: a discount or a phased invoice schedule not reflected in the CRM.
- Invoices with no deal: renewals, upsells or self-serve sales that never went through the CRM.
- Several invoices for one deal: milestone billing — sum invoices per deal before comparing.
For subscription businesses, matching CRM contacts to billing customers is the first step — see matching HubSpot contacts to Stripe customers.
Report it as revenue leakage
Sum the amounts of closed-won deals with no invoice older than 30 days: that's revenue you've won but not billed. Present it per sales owner and month, and track it until it reaches zero — it's one of the easiest wins in a finance review.
What to export from each system
Column names differ between CRMs and billing tools, and between versions of the same tool, so treat the lists below as typical fields rather than exact headers. What matters is that each side has an identifier, a customer, an amount and a date.
| Side | Fields | Why |
|---|---|---|
| CRM deals | Deal or opportunity ID, account name, amount, currency, stage, close date, owner | Stage filters to closed-won; owner lets you report leakage per rep |
| Invoices | Invoice number, customer, date, net amount, currency, reference or custom field holding the deal ID | Net amount compares like with like; the reference is your best key |
| Optional | Customer domain or company number on both sides | A sturdier key than a typed account name |
Filter the CRM export to closed-won deals before matching, and export invoices from slightly before the earliest close date to some weeks after the latest, so deals invoiced in advance or late still find their invoice.
Formulas for each version
In Excel 365 and Excel 2021, XLOOKUP returns the matching invoice number directly. In Excel 2019 there is no XLOOKUP or FILTER, so use INDEX/MATCH on the key and COUNTIFS for the yes/no check. Google Sheets supports XLOOKUP and FILTER as well, and the COUNTIFS date-window formula works unchanged.
Excel 365 / Google Sheets
Invoice no. =XLOOKUP([@[Deal ID]], Invoices[Deal ID], Invoices[Invoice no.], "Not invoiced")
Excel 2019
Invoice no. =IFERROR(INDEX(Invoices[Invoice no.], MATCH([@[Deal ID]], Invoices[Deal ID], 0)), "Not invoiced")If you match on the fallback key of account and amount, keep the 30-day window: two deals of the same size for the same customer in one year are common with renewals, and without the date condition both deals would match the first invoice.
Second example: milestone and phased billing
When deals are billed in stages, one deal links to several invoices. Total the invoices per deal first, then compare with the deal amount:
Invoiced to date =SUMIFS(Invoices[Net amount], Invoices[Deal ID], [@[Deal ID]])
Still to bill =[@Amount] - [@[Invoiced to date]]| Deal | Amount | Invoices | Invoiced to date | Still to bill | Reading |
|---|---|---|---|---|---|
| D-580 | 24000 | 3 × 8,000 | 24000 | 0 | Fully billed |
| D-584 | 18000 | 2 × 6,000 | 12000 | 6000 | Third instalment due — check the schedule |
| D-590 | 9000 | 1 × 9,900 | 9900 | -900 | Billed above deal: price change not updated in CRM |
A positive Still to bill amount is only leakage if the next instalment is overdue under the contract, so add the expected billing date to the deal if your CRM holds one.
Troubleshooting
| Symptom | Likely cause | Fix |
|---|---|---|
| Almost nothing matches on account name | Legal name on invoices, trading name in the CRM | Match on domain or a mapping table of CRM account to billing customer |
| Amounts differ by a fixed percentage | One side includes tax | Compare net amounts on both sides |
| Foreign-currency deals never match | CRM amount converted to a reporting currency | Match on the original currency amount, or allow a tolerance |
| One invoice matches two deals | Two equal deals for the same customer | Tighten the date window or add the deal ID to the invoice reference |
| Deal IDs look identical but fail | IDs stored as numbers on one side and text on the other | Convert both to text with TEXT or by prefixing an empty string |
Repeating the check every month
- Export closed-won deals for the month and invoices for the month plus the next 30 days.
- Paste them into the same tables in your matching workbook so every formula updates.
- Review the Not invoiced list with sales owners and record a reason against each deal.
- Carry unresolved deals into next month's list until they are invoiced or the deal is reopened.
To check the result, the number of matched deals plus unmatched deals should equal the closed-won count from the CRM report, and the sum of matched invoice amounts should agree with the invoice total in your billing tool for those invoices. If you'd rather ask the question in plain words across both exports, Analistable runs the same join in your browser without uploading the rows. For the underlying join techniques, see joining two spreadsheets on a common column and the reconciliation guide.
Common mistakes
- Matching on the gross invoice total against a net deal amount, which makes every deal look over-billed.
- Leaving open, lost or reopened deals in the export, so they show as unbilled.
- Treating credit notes as invoices: net them off against the original invoice before summing per deal.
Frequently asked questions
- How do I find deals that were never invoiced?
- Match closed-won deals to invoices on a deal ID, or on customer and amount within a date window, and filter deals with no match.
- What if one deal has several invoices?
- Sum invoice amounts per deal with SUMIFS and compare the total with the deal amount.
- Which CRM exports work?
- Any export that includes deal ID, account, amount, stage and close date — Salesforce, HubSpot and Pipedrive all provide these.
- How do I find invoices that don't have a deal?
- Build the same key on the invoice side and filter invoices whose key has a COUNTIFS of zero in the deals export.
- Should I match on deal amount or invoice total?
- Match on whichever both systems hold consistently, usually the amount excluding tax, then sum invoices per deal.
- How often should I run this check?
- Monthly, after finance closes the month, so unbilled deals are caught within weeks rather than at year end.
- Can I do this in Excel 2019 without XLOOKUP?
- Yes. Use INDEX and MATCH to return the invoice number and COUNTIFS with the date window for the yes/no check; both work in Excel 2019.
- How do I handle deals in another currency?
- Match on the original currency amount where both systems hold it; otherwise allow a small tolerance on the converted amount and review those matches by hand.