How to do a three-way match in Excel
By the Analistable team · Updated · 6 min read
A three-way match checks three records before you pay a supplier: the purchase order (what you agreed), the goods received note (what arrived) and the invoice (what they billed). Join them on PO number + line (or PO + SKU), then flag invoices for more units than were received, or at a higher price than the PO.
Part of our guide: How to reconcile data in Excel
Build one table per PO line
Ordered =[@Qty] (from the PO table)
Received =SUMIFS(GRN[Qty], GRN[PO], [@PO], GRN[SKU], [@SKU])
Invoiced =SUMIFS(Inv[Qty], Inv[PO], [@PO], Inv[SKU], [@SKU])
Inv price =XLOOKUP([@PO]&"|"&[@SKU], Inv[PO]&"|"&Inv[SKU], Inv[Unit price], "")SUMIFS handles part deliveries and part invoices: three deliveries of 40 add up to 120 received.
Worked example
| SKU | Ordered | PO price | Received | Invoiced | Invoice price | Result |
|---|---|---|---|---|---|---|
| BOX-S | 500 | 0.42 | 500 | 500 | 0.42 | Pay |
| BOX-M | 300 | 0.55 | 240 | 300 | 0.55 | Hold: 60 invoiced but not received |
| TAPE | 50 | 1.8 | 50 | 50 | 1.95 | Hold: price above PO |
Tolerances
Most teams allow small differences — for example a price within 2% or quantity within 1 unit — to avoid holding invoices over rounding. Put the tolerance in a cell and reference it:
=IF(AND([@Invoiced] <= [@Received], [@[Invoice price]] <= [@[PO price]] * (1 + Tolerance)), "Pay", "Hold")What usually causes holds
- Goods received but the GRN not entered yet — chase the warehouse before the supplier.
- Price increases the supplier applied without a PO change.
- Substituted SKUs: the invoice uses a different code for an equivalent item.
- Freight or surcharges on the invoice that weren't on the PO.
Matching on two columns (PO and SKU) is a general technique — see reusable two-key lookups.
Matching in Power Query
For hundreds of lines a month, load the three exports into Power Query, group GRN and invoice lines by PO and SKU (Group By → Sum of quantity), and merge both onto the PO lines with a left outer join. Add the Pay/Hold column as a conditional column and refresh with each month's files. See Power Query's join kinds.
Two-way match for services
Services have no goods received note. Match the invoice to the PO on amount and period instead, with the approver's sign-off taking the place of the receipt.
The three exports
| Purchase orders | Goods received notes | Supplier invoices |
|---|---|---|
| PO number, supplier | GRN number, PO number | Invoice number, supplier, PO number |
| SKU or item code, description | SKU, quantity received | SKU, quantity invoiced |
| Quantity ordered, unit price | Date received, received by | Unit price, line total, VAT |
The PO number is the key that ties all three together, so the most important clean-up is making it identical everywhere: same prefix, no spaces, stored as text. Invoices without a PO number go on a separate list for the buyer to identify before any matching starts.
Second example: deliveries and invoices that don't line up in time
PO 7790 is for 120 units of LABEL-R at £2.50. The supplier delivers 40 a week and invoices whenever it likes. The match changes from week to week:
| Date | Received to date | Invoiced to date | Result | Received not invoiced |
|---|---|---|---|---|
| 31 Aug | 80 | 60 | Pay the 60 invoiced | 20 × 2.50 = 50.00 |
| 30 Sep | 120 | 120 | Pay | 0 |
At 31 August the goods for 20 units have arrived but no invoice has. That £50 is a cost you owe but haven't been billed for; many businesses accrue it as goods received not invoiced. The opposite case — invoiced but not received — is a hold, as in the first example.
The match in SQL
Summing receipts and invoices per PO line before joining avoids the row multiplication you get from joining three detail tables directly:
WITH r AS (SELECT po, sku, SUM(qty) AS received FROM grn GROUP BY po, sku),
i AS (SELECT po, sku, SUM(qty) AS invoiced, MAX(unit_price) AS inv_price
FROM inv GROUP BY po, sku)
SELECT p.po, p.sku, p.qty AS ordered,
COALESCE(r.received, 0) AS received,
COALESCE(i.invoiced, 0) AS invoiced,
p.price, i.inv_price,
CASE WHEN COALESCE(i.invoiced, 0) > COALESCE(r.received, 0) THEN 'Hold: quantity'
WHEN i.inv_price > p.price * 1.02 THEN 'Hold: price'
ELSE 'Pay' END AS result
FROM po_lines p
LEFT JOIN r ON r.po = p.po AND r.sku = p.sku
LEFT JOIN i ON i.po = p.po AND i.sku = p.sku
ORDER BY p.sku;On PO 7781 above (BOX-M received in two deliveries of 120) it returns BOX-M as Hold: quantity, BOX-S as Pay and TAPE as Hold: price, because £1.95 is more than 2% above £1.80. For why pre-aggregating matters, see SQL joins across multiple tables.
Troubleshooting
| Symptom | Likely cause | Fix |
|---|---|---|
| Received quantity doubled | Joined GRN detail rows before summing | Sum per PO and SKU first, then join |
| Invoice line matches no PO line | Supplier's own item code on the invoice | Keep a supplier-code to SKU mapping table |
| Quantities in different units | PO in boxes, invoice in units | Convert with a pack-size column before comparing |
| Same invoice processed twice | Re-sent by email and keyed again | COUNTIFS on supplier + invoice number above 1 |
| Price hold on every line | PO prices exclude VAT, invoice prices include it | Compare net prices on both sides |
Duplicate invoices deserve their own check before payment runs — see finding vendors billed twice.
Doing it in Google Sheets
Import the three exports to separate tabs. SUMIFS works the same way, and two-key lookups can use FILTER, for example =IFERROR(INDEX(FILTER(Inv!E:E, Inv!A:A=A2, Inv!B:B=B2), 1), "") for the invoice price. For more than a few thousand lines, Sheets recalculation slows down noticeably, and a query or Power Query becomes the better tool.
Checking the result
Before releasing a payment run, check three totals: the invoices marked Pay add up to the payment run total; no invoice is on both the Pay and Hold lists; and every invoice received in the period is on one list or the other, including those without a PO. Then review holds older than your payment terms, since a long-held invoice is often a GRN that was never entered rather than a supplier error. A quick monthly review of holds by supplier also shows who most often invoices ahead of delivery or above the agreed price.
Which method to use
Many accounting and purchasing systems do the three-way match themselves when POs and receipts are entered in them. A spreadsheet is useful when receipts are recorded in a separate warehouse system, when catching up a backlog, or for a periodic audit of the system's own matching. Under a few hundred PO lines a month, SUMIFS in one workbook is enough; above that, the grouped merge in Power Query or SQL is faster and easier to refresh.
Common mistakes
- Matching the invoice total to the PO total instead of line by line, which hides a price increase offset by a short delivery.
- Paying an invoice because the goods arrived, without checking the quantity received.
- Leaving a PO open after the final invoice, so a duplicate invoice still finds an unmatched balance to sit against.
Frequently asked questions
- What is a three-way match?
- Checking that the purchase order, goods received note and supplier invoice agree on quantity and price before paying.
- How do I do a three-way match in Excel?
- Put PO lines in one table and use SUMIFS on PO number and SKU to bring in received and invoiced quantities, then flag differences.
- What's a two-way match?
- Comparing only the PO and invoice, without the goods received record — used for services or low-value purchases.
- What tolerance should a three-way match use?
- It's a policy choice. Many teams allow a small percentage on price and a unit or two on quantity; anything larger needs approval.