Three-way matching Excel template
A spreadsheet that compares a supplier invoice against its purchase order and goods receipt, line by line, and tells you which lines to hold. The formulas are already written: paste your three documents into the input sheets and read the status column.
Free, no signup, no email address. Works in Excel, Google Sheets and LibreOffice Calc. No macros, no external links, and every figure in the sample data is synthetic.
XLSX, 5 sheets, about 10 KB. Prefer not to download anything? The checker does the same comparison in your browser.
What the template does
Three-way matching means checking that what you were billed for matches what you ordered and what you actually received. The template does that comparison at line level, on pre-tax unit prices.
It deliberately never compares document totals. Totals include tax, they cannot represent a partial delivery or a partial invoice, and they let two opposite errors cancel out into a figure that looks correct. An overcharge of 40 on one line and an undercharge of 40 on another nets to zero at the bottom of the page and to two problems at line level.
What is inside the file
| Sheet | What goes in it |
|---|---|
| Instructions | How to fill it in, how the two variances are calculated, and what each status means. |
| Matching | The worked result. One row per item code, with ordered, received and invoiced quantities pulled from the other sheets, both variances, and a status. This is the only sheet with formulas. |
| Purchase order | PO number, supplier, item code, description, ordered quantity, PO unit price. |
| Goods receipts | Receipt number, supplier, item code, description, received quantity. No price: a goods receipt does not carry one. |
| Invoice lines | Invoice number, supplier, item code, description, invoiced quantity, invoice unit price. |
When to use it
- A supplier invoice does not agree with the purchase order and you need to show exactly which lines and by how much.
- You are closing a month and need to see what has been received but not yet invoiced — the GRNI balance.
- You are writing or defending a matching tolerance and want to see what your thresholds actually do to real lines.
- You are checking a supplier's first invoices before putting them on a standing order.
How to complete it
- Paste your purchase order lines into the Purchase order sheet.
- Paste your goods receipt lines into the Goods receipts sheet.
- Paste the supplier invoice lines into the Invoice lines sheet.
- List every item code you want checked in column C of the Matching sheet.
- Set your tolerances in cells B2 and B3.
- Read the Matching status column. Anything that is not Match needs a decision.
Lines are matched on the item code, so the same code has to appear on all three sheets. If your supplier uses their own codes, map them to yours in a lookup column first. That mapping problem is the single most common reason spreadsheet matching quietly stops working.
How price variance is calculated
Price variance is the unit price difference multiplied by what you were actually billed for:
price variance = (invoice unit price − PO unit price) × invoiced quantity
In the sample data, one line is ordered at 12.00 and invoiced at 13.20 across 60 units. The unit gap is only 1.20, which looks harmless, but the line carries 72.00 of price variance.
How quantity variance is identified
Quantity variance is the quantity difference valued at the price you agreed:
quantity variance = (invoiced quantity − ordered quantity) × PO unit price
The two are kept in separate columns on purpose. A variance that is entirely quantity has a different owner and a different fix from one that is entirely price: the first is a receiving or ordering conversation, the second is a pricing one. A single blended "variance" figure hides which of the two you are looking at.
Setting the tolerances
The template ships with an absolute tolerance of 1.50 and a percentage tolerance of 2%. A line is flagged when it exceeds either threshold, and the comparison is strict — a value exactly equal to the threshold sits inside tolerance.
Those two numbers are a starting point, not a standard. The honest way to set them is to take 90 days of your own matched lines, look at where the variance distribution stops being noise, and put the thresholds there. Both are editable in cells B2 and B3.
The eight matching statuses
Listed most severe first. Where a line qualifies for more than one, the most severe wins.
| Status | What it means | Typical action |
|---|---|---|
| No PO line | Invoiced, with nothing ordered. | Route to the requester for retrospective approval. |
| No goods receipt | Ordered and invoiced, never receipted. | Hold. Chase the warehouse before paying. |
| Over-invoiced | Invoiced more than was received. | Pay the received quantity, hold the rest. |
| Price variance | Unit price outside tolerance. | Ask for a credit note or an updated PO. |
| Quantity variance | Invoiced quantity outside tolerance against the order. | Confirm the over- or under-delivery was authorised. |
| Not invoiced | Ordered and received, no invoice yet. | This is your GRNI balance. Accrue it. |
| Under-invoiced | Invoiced less than was received. | Expect a second invoice for the balance. |
| Match | Within tolerance on both dimensions. | Nothing to do. |
The sample data, solved
The file opens on a worked example built to produce every status once. All of it is synthetic.
| Item | Ordered | Received | Invoiced | PO price | Inv. price | Qty var. | Price var. | Status |
|---|---|---|---|---|---|---|---|---|
| SKU-1001 | 40 | 40 | 40 | 24.50 | 24.50 | 0.00 | 0.00 | Match |
| SKU-1002 | 30 | 34 | 34 | 8.90 | 8.90 | 35.60 | 0.00 | Quantity variance |
| SKU-1003 | 60 | 60 | 60 | 12.00 | 13.20 | 0.00 | 72.00 | Price variance |
| SKU-1004 | 100 | 100 | 120 | 1.15 | 1.15 | 23.00 | 0.00 | Over-invoiced |
| SKU-1005 | 25 | 0 | 25 | 6.40 | 6.40 | 0.00 | 0.00 | No goods receipt |
| SKU-1006 | 20 | 20 | 0 | 3.75 | 0.00 | −75.00 | 0.00 | Not invoiced |
| SKU-1007 | 0 | 12 | 12 | 0.00 | 18.40 | 0.00 | 220.80 | No PO line |
| SKU-1008 | 36 | 37 | 36 | 9.80 | 9.80 | 0.00 | 0.00 | Under-invoiced |
| SKU-1009 | 12 | 12 | 12 | 21.00 | 21.00 | 0.00 | 0.00 | Match |
| SKU-1010 | 8 | 8 | 8 | 45.00 | 45.90 | 0.00 | 7.20 | Match |
The last row is the one worth studying. It is 0.90 over on a 45.00 line — exactly 2%. Because the comparison is strict, exactly 2% is inside tolerance and the line passes. Move the threshold to 1.9% and the same line fails. That is what a tolerance actually does, and it is why the number deserves more thought than it usually gets.
Where a spreadsheet stops working
This template is honest about its ceiling. It cannot do the following, and no amount of extra formulas will fix that:
- One invoice across several purchase orders. The matching sheet assumes one order at a time.
- Suppliers who rename items between documents. Matching is on an exact item code. "Copier paper A4" and "A4 paper, copier" are two different items to a spreadsheet.
- Partial deliveries over time. You can total the receipts, but the file keeps no history of what was already approved and paid.
- An audit trail. There is no record of who accepted which variance, or when.
- Duplicate invoices. The same invoice pasted twice simply doubles the quantities.
Past roughly 200 to 300 invoice lines a month, keeping the item codes aligned by hand costs more than the errors the spreadsheet catches. That is the point where the file stops being a control and starts being a second thing to reconcile.
When automated matching becomes worth it
The switch is worth making when at least two of these are true: invoices arrive from more than a handful of suppliers, item descriptions vary between the order and the invoice, invoices routinely span several orders, or somebody needs to prove afterwards who approved a variance.
ininvoice runs the same line-level comparison as this template, with the same two tolerances, but it reads the documents itself, matches descriptions that are not identical, and keeps the audit trail. If you only want to see the arithmetic without installing anything, the invoice matching checker does that in your browser.
Questions
- Is the template really free?
- Yes. The file downloads directly, with no signup and no email address. There is no gated version and no watermark.
- Does it work in Google Sheets and LibreOffice?
- Yes. It uses only SUMIF, INDEX, MATCH, IFERROR, IF, OR, AND and ABS, which behave identically across Excel, Google Sheets and LibreOffice Calc. There are no macros and no external links.
- Can I change the tolerances?
- Yes, in cells B2 and B3 of the Matching sheet. Every status recalculates immediately.
- Is any of the sample data real?
- No. Every supplier, item and figure in the workbook is invented for the example.
Related
- Invoice matching checker — the same comparison, in your browser, nothing to download.
- Three-way matching explained — what the control is and why header totals fail it.
- How to calculate price variance — the arithmetic in more depth.
- The total-matching trap — why comparing document totals passes audit and still leaks money.
- GRNI reconciliation — what to do with the "Not invoiced" lines.
- Why Excel stops scaling at 300 invoices — the ceiling, with numbers.
When the spreadsheet becomes the bottleneck
ininvoice reads your invoices, orders and goods receipts and runs this comparison for you, line by line, with the audit trail attached.
Create a free account20 documents per month, no card required.