Free guide: P2P controls
Three-way match in Excel: check PO, receipt and invoice agree before payment
A three-way match checks that the purchase order, the goods receipt and the invoice agree before a supplier is paid. Most ERP systems do it automatically, but analysts often need to test it, investigate exceptions or rebuild it for an audit. Here is how to do it in Excel.
1. The three tables and their grain
| Table | One row is | Key fields |
|---|---|---|
| PO lines | One line of a purchase order | PONumber, POLine, Supplier, Quantity, UnitPrice |
| Goods receipts | One receipt against a PO line | PONumber, POLine, QuantityReceived, ReceiptDate |
| Invoice lines | One line of a supplier invoice | InvoiceNumber, PONumber, POLine, Quantity, UnitPrice, Amount |
Load each with Power Query as a table. Create a match key in each: PONumber & "-" & POLine. If receipts arrive in several deliveries, group them in Power Query to one row per key with the total quantity received.
2. Bring the three together
Start from the invoice lines, because the question is "can we pay this invoice?". Add columns:
PO price =XLOOKUP([@Key], PO[Key], PO[UnitPrice], "NO PO")
PO quantity =XLOOKUP([@Key], PO[Key], PO[Quantity], "NO PO")
Received qty =XLOOKUP([@Key], GR[Key], GR[QuantityReceived], 0)
Price diff % =IF(ISNUMBER([@[PO price]]), [@UnitPrice]/[@[PO price]]-1, NA())
Qty vs receipt =[@Quantity]-[@[Received qty]]3. Match rules and tolerances
Agree tolerances with the AP and procurement owners and write them down. A common pattern, to adapt to your policy:
Status =IFS(
[@[PO price]]="NO PO", "No PO",
[@[Received qty]]=0, "Not received",
[@[Qty vs receipt]]>0, "Billed more than received",
ABS([@[Price diff %]])>Tolerance, "Price variance",
TRUE, "Matched")Put Tolerance in a named input cell (for example 2%) rather than inside the formula, so it is visible and easy to change.
4. What to report
- Match rate by invoice count and by value. Name which one you show.
- Exception list sorted by value, with the status, supplier and owner. This is what AP acts on.
- Repeat offenders: suppliers or buyers with the most exceptions over several months, which points to a process cause.
5. Traps
- Partial deliveries. Sum receipts per PO line before matching, or partial shipments look like shortages.
- Service purchases. Services often have no goods receipt. Many organisations use a two-way match (PO and invoice) plus a service confirmation. Do not report them as "not received" without checking the policy.
- Units of measure. A PO in boxes and an invoice in units will never match. Normalise first.
- Currency and tax. Compare net prices in the PO currency.
- An exception is not proof of wrongdoing. It is a reason to check, and many have a valid explanation.
Practise with known answers. The Starter Kit's practice dataset covers PO and contract controls with expected values for every check. Its invoices carry PO flags rather than receipt lines, so use this guide's structure on your own data or a test file for the receipt step.
Go further
The Starter Kit teaches this method end to end on the practice dataset: a 54-page guide, a click-by-click Excel and Power BI build guide, 29 exercises and 3 case studies with worked answers, 28 AI prompts, a self-checking workbook and a 29-KPI dictionary.
CA$15 once (about US$11), plus local sales tax where it applies, shown at checkout. Secure checkout by Stripe. Instant download. All sales final.
Free one-page KPI cheat sheet: download the PDF, no email needed.