Practical worksheet guide
Set up a three-way match in Excel
Use one stable line key, aggregate partial documents, and preserve source rows. Download the synthetic files to test your worksheet.
Start with a conservative line key
Normalize case and extra spaces, but keep punctuation and leading zeroes. A value such as 00045 is not safely interchangeable with 45.
=UPPER(TRIM([@[PO Number]])) & "|" & UPPER(TRIM([@[Item ID]]))
Minimum useful columns
| PO export | Receipt export | Invoice export |
|---|---|---|
| PO number Vendor Item/SKU Ordered quantity Ordered unit cost | PO number Item/SKU Received quantity Receipt ID/date Vendor if available | Invoice number PO number Vendor Item/SKU Invoice quantity Invoice unit cost Line total if available |
Aggregate before comparing
Use SUMIFS for received and invoiced quantities so one PO line can have several receipts and several invoices.
=SUMIFS(Receipts[Received Qty], Receipts[Match Key], [@[Match Key]])
=SUMIFS(Invoices[Invoice Qty], Invoices[Match Key], [@[Match Key]])
Then flag invoice quantity above receipts, invoice quantity above ordered quantity, and invoice unit price outside your explicit tolerance. Keep a separate state for missing or ambiguous keys; do not turn the closest-looking SKU into an automatic financial match.
Download synthetic sample files
These files are invented and contain clean, price, receipt, duplicate, and unresolved cases. They are safe to use in a worksheet or the MatchThree importer.
Skip the workbook wiring
Run the same synthetic case instantly, then export your own files when ready.