Three-Way Matching Data Extraction: PO, GRN, Invoice

Three-way matching data extraction is the data-preparation step that produces a single comparison spreadsheet from the purchase order, the goods received note, and the supplier invoice. The output is keyed by PO number, PO line, supplier code, item code, and unit of measure, with PO, GRN, and invoice columns sitting side by side and computed match flags for quantity, price, and supplier. The exception code column on each row drives triage — quantity short or over, price variance, missing GRN, missing invoice, duplicate, currency mismatch, tax-rate mismatch, line-not-on-PO, wrong supplier.

For any AP team not on a unified ERP with EDI-fed supplier data, three-way matching is the extraction problem. The matching logic is trivial once the three documents share a common shape; the work is getting them there. POs arrive as PDFs from procurement, GRNs as scans or hand-written chits or warehouse-system exports from receiving, and invoices as PDF email attachments from suppliers. Three formats, three sources, three timings, one schema.

The conceptual question — what makes a match two-way, three-way, or four-way — sits in our conceptual explainer of 2-way, 3-way, and 4-way invoice matching, and this article assumes you have that part settled. What follows is the data shape: the comparison spreadsheet schema, the line-key rule that survives real-world line-number drift, where tolerance sits, the exception codes worth using, where each document comes from, how to triage what the spreadsheet surfaces, and which tooling option fits the volume you are actually running.

The Comparison Spreadsheet Schema

Every row in the comparison spreadsheet represents a single comparable unit — typically one PO line and the receipt and invoice events that match against it — surfacing the three documents' values side by side with computed match flags. The point of the schema is to make the matching question answerable by reading across one row.

Key columns. Five columns carry the join logic: PO number, PO line number, supplier code (or supplier identifier), item code or SKU, and unit of measure. Every later column hangs off these. The composite of (supplier code, item code, unit of measure) is the part that survives real-world line drift across the three documents, and the next section walks the rule that uses it.

PO columns. PO date, PO line description, PO line quantity ordered, PO line unit price, PO line total. These capture the buyer's intent at the point of order — what was authorised, in what quantity, at what price.

GRN columns. GRN number, GRN date, GRN line quantity received, GRN line quantity variance (received minus ordered), GRN condition flag. These capture the fact of receipt, including any short, damaged, or rejected portion the receiving team recorded.

Invoice columns. Invoice number, invoice date, invoice line quantity billed, invoice line unit price, invoice line total, invoice tax amount. These capture what the supplier is actually billing for, in their own line sequence and with their own pricing.

Computed match columns. Quantity match flag, price match flag, supplier match flag, exception code, exception note. These are derived from the source columns by a formula or rule — they are not extracted. They express the matching policy's outputs in the data.

The natural instinct when building this — pulling PO, GRN, and invoice data into one spreadsheet — is to lay it out wide: one row per PO line, with side-by-side columns for each document's view of that line. Wide-format works for a textbook example where every PO line maps to exactly one receipt and exactly one invoice. As soon as one PO line maps to multiple receipts (split delivery) or multiple invoices (progressive billing), wide-format collapses — you cannot fit two receipt quantities and three invoice quantities into a single row of fixed columns without inventing parallel-column gymnastics that no formula can read cleanly. Long-format is the only shape that survives: one row per (PO line × receipt event × invoice event) combination, with the keys carrying the row identity and the document columns carrying values from whichever event that row represents. The messy variant of partial deliveries and progressive billing comes back in a later section; the schema is designed so it absorbs that variant without restructuring.

A practical aside on services: this column list assumes goods-line-level matching. For service categories the GRN is replaced by a Service Entry Sheet, and the same schema applies line-for-line — the section on document sources picks that thread back up.

This schema is heavier than the matching-logic-light spreadsheet examples an AP clerk has likely seen elsewhere. That is intentional. It captures everything an auditor will ask for in a control walkthrough and everything a supplier will ask for in a dispute. Simpler schemas hide work that resurfaces during exception triage, when the missing column you skipped six weeks ago becomes the column you now need to reconstruct from the source documents.

Line-Key Discipline — The Composite Key That Survives Real-World Drift

Real-world line numbering does not align across PO, GRN, and invoice. The PO carries the buyer's line 1, 2, 3 sequence in the order procurement entered the requisition. The GRN may consolidate, split, or re-order those lines based on how the receiving team grouped items as they came off the truck — a single delivery against two PO lines for the same item often shows up as one line on the goods received note. The supplier's invoice uses the supplier's own billing line sequence, which reflects how the supplier's system batches and prices items and often has nothing to do with the PO's line order. Joining on PO line number alone produces silently mismatched rows that look matched, balance to the totals, and quietly reconcile the wrong items against each other.

The composite line key is the rule that holds. Three components plus one disambiguator: (supplier code, item code or SKU, unit of measure) plus a sequence counter scoped to that key when the same item appears on more than one PO line. Each component carries weight. Supplier code disambiguates across suppliers running the same SKU — two distributors of the same manufacturer's product is the common case. Item code is the only identifier that travels stably across the buyer and the supplier systems; descriptions vary, line numbers vary, but the SKU is the anchor. Unit of measure catches the each-versus-case unit traps before they turn into quantity variances. The sequence counter handles the case where the same item legitimately appears on multiple PO lines and needs to stay disambiguated through the matching.

A worked example makes the rule concrete. The PO has three lines: line 1 is item A at 100 each, line 2 is item B at 50 cases, line 3 is item A at 25 each — a second order of item A entered later in the requisition. The supplier invoices in their own sequence. Their invoice line 1 consolidates the two item-A PO lines into a single billing line of 125 each. Their invoice line 2 bills item B at 50 cases. The GRN, generated by receiving, records two events: a partial receipt on day one of 90 each of item A and 50 cases of item B; a second receipt on day three of the remaining 35 each of item A. Joined on the buyer's PO line number, this never reconciles cleanly — invoice line 1 has no PO line 1 it matches one-to-one with, the GRN has two events against PO line 1 and none against PO line 3 by buyer numbering, and the totals only happen to balance.

Joined on the composite key it lines up. The key for the first item-A PO line is (supplier, item A, each, 1); the key for the second item-A PO line is (supplier, item A, each, 2). The invoice's consolidated item-A line maps against both — long-format spreads it across both rows, allocating 100 each to the first and 25 each to the second based on PO quantity. The GRN's day-one receipt of 90 each carries the same composite key (supplier, item A, each) and allocates 90 to the first PO line, leaving 10 short on that row; the day-three receipt of 35 each allocates the remaining 10 against the first PO line (clearing the short) and 25 against the second PO line. Item B is its own composite key (supplier, item B, case) and resolves trivially in one row. The match flags then run row by row across consistent quantities. The matching survives the supplier's consolidation, the receiver's split delivery, and the two-different-orders-of-the-same-item case that breaks PO-line-number-only joins.

Keep the secondary keys in the row alongside the composite key. PO number stays because exception triage chases by PO when an issue is raised. The supplier's own invoice line number stays because supplier dispute conversations reference the supplier's line number — "your invoice line 1 is consolidating two of our PO lines" only makes sense if you can quote the supplier's number back to them. The receiving team's GRN line number stays for the same reason on the receiving side. These are reference columns, not join keys.

The composite key prevents three specific failure modes that every spreadsheet-based three-way match line-level reconciliation sees eventually. Silently joined rows where the PO line and the invoice line happen to share the same buyer line number but are actually different items — a PO line 3 for item C and an invoice line 3 for item D, joined on the number, will look matched in totals while reconciling the wrong items against each other. Quantity variances that look like real shortages but are actually unit-of-measure conversion artifacts, where the supplier bills 10 cases and the receiver counts 120 eaches against a 12-pack; without UoM in the key, the variance reads as a quantity short. And split-line errors, where a PO line's split-delivery receipts get inadvertently summed against the wrong invoice line because the join logic could not see that one supplier billing line spanned two PO lines.

When you set up the join in any form — Excel, BI tool, ERP matching engine — match PO line to invoice line by what the item is, not by which line number it sits on.

Tolerance Belongs in Computed Columns, Not in Extraction

The separation of concerns is straightforward and worth holding: extraction captures the raw value from each document — quantity, price, supplier — exactly as the document stated it. Tolerance is applied downstream, in a computed column that flags whether the variance is inside or outside policy. The schema must support tolerance without ever baking it in. A row where the price variance fell inside tolerance should still show both the raw PO price and the raw invoice price; the match flag is what changes between within and outside, not the underlying numbers.

The practical consequence of this discipline is that policy changes do not require re-extracting anything. Tightening the price tolerance from 5% to 2% touches only the formula column; the raw price columns are untouched, the audit trail is intact, and every row that was within tolerance under the old rule and outside it under the new rule lights up immediately without any data prep work.

A few reference figures are worth naming as common starting points, not as recommendations. Price tolerance commonly lives in the 1–5% range, sometimes paired with a flat-dollar floor of $5 to $10 for low-value lines where a percentage rule produces noise — a 4% variance on a $3.50 line is 14 cents, and chasing 14-cent exceptions burns more than it saves. Quantity tolerance is usually zero, but with an explicit allowance where unit-of-measure conversion or pack-size variance is in play; a 12-pack short-shipped by one each is not a quantity variance, it is a unit-conversion question the schema needs to reconcile before flagging. Tax tolerance is near-zero because tax rates round predictably, and a tax mismatch usually signals a coding error or a jurisdiction error, not a rounding artifact. These figures appear across ERP matching policies and AP control frameworks; they are the numbers most practitioners recognise, not the article's view of where the right thresholds sit.

The flag logic sits on the schema as a set of computed columns reading the raw ones. Quantity match flag reads PO quantity, GRN quantity, and invoice quantity, applies the tolerance rule, and writes within tolerance, outside tolerance, or missing input. Price match flag reads PO unit price and invoice unit price, applies the price tolerance rule (percentage, flat-dollar floor, or both), and writes the same set of values. Supplier match flag reads the PO supplier code and the invoice supplier code and writes match or mismatch. The exception code column reads the combination of flags and the raw values and writes the named code from the taxonomy in the next section. Every flag is a derivation; nothing in the flag column is data the documents carried.

This shape carries the audit-control role three-way matching exists to play. The control cross-verifies authorization (the PO), receipt (the GRN), and billing (the invoice) before payment. FAR 32.905 on payment documentation codifies the documentation chain in U.S. federal procurement: every invoice payment must be supported by a receiving report or other Government documentation authorizing payment. The schema's job in that picture is not just convenience; it is control infrastructure. The auditor reading a row needs to see the raw PO price, the raw invoice price, and the match flag the policy produced from them. If the schema collapses the raw values into a single "matched" indicator, the auditor cannot reconstruct what the policy allowed and what it should have allowed.

For the financial controller specifically, the work is not running every PO–GRN–invoice match by hand. The controller reviews the exception queue and signs off on the control's effectiveness — month over month, the count of exceptions by code, the aging of open rows, the supplier concentration of variances. The schema serves that review by making both the raw values and the policy decision auditable in one row. A controller can answer "did the policy hold?" without re-extracting anything; an auditor can answer "would a tighter policy have caught this?" by reading across the row. Tolerance policy itself — what percentages fit your business — is finance leadership's call to make on top of the schema, not a property of the schema.

The Exception Codes Your AP Team Actually Uses

The exception code column converts the combination of match flags into a single named code that drives triage. One code per row, drawn from a fixed list. The flag columns answer what is wrong; the code column answers what is wrong specifically enough to act on.

• Quantity short — invoice quantity matches PO quantity, but GRN quantity is lower; goods did not arrive in full.
• Quantity over — GRN quantity is higher than PO quantity; receiver accepted more than was ordered.
• Price variance — invoice unit price is outside the price-tolerance window against PO unit price.
• Missing GRN — invoice is present, PO is present, no goods receipt has been recorded against the PO line.
• Missing invoice — PO and GRN are present, no supplier invoice has been received; this feeds the GRNI accrual.
• Duplicate invoice — the supplier has billed the same PO line on a second invoice, intentionally or otherwise.
• Wrong supplier — the invoice's supplier code does not match the PO's supplier code; either the PO was misrouted or the invoice carries the wrong supplier identifier.
• Currency mismatch — invoice currency differs from PO currency; the matching cannot proceed until conversion is reconciled.
• Tax-rate mismatch — invoice tax rate differs from the rate expected for the supplier and the jurisdiction; usually a coding error, not a rounding artifact.
• Line-not-on-PO — invoice carries a line whose composite key does not appear on the PO; either an unauthorized billing or a missed PO line.

The instinct to roll these up into buckets — quantity exceptions, price exceptions, supplier exceptions — is intuitive and useless for triage. The code drives the action, not the bucket. Quantity short and quantity over both involve quantity, but the first is chased with the supplier (where are the missing goods?) and the second is chased with the receiving team (why did we accept over-shipment?). Missing GRN and missing invoice are both missing-document codes, but one goes to procurement to confirm whether the receipt happened off-system and the other goes to finance to book the GRNI accrual. Coded vocabulary, applied one row at a time, is what turns the comparison spreadsheet into a queue.

Where the Three Documents Come From — and What Makes Each One Messy

The three documents come from three different teams at three different times in three different formats. Each leg has its own pattern of failure modes, and naming them is enough — full coverage of each extraction step is its own topic.

PO extraction. Buyer-generated, usually the cleanest input. Procurement systems can typically export POs as structured data, and where they cannot, the PO arrives as a PDF with a consistent layout because the buyer controls the template. Header data — PO number, PO date, supplier code — is rarely the bottleneck. The pitfall is line-level: PO line quantities, unit prices, and unit-of-measure values have to land in the schema columns named earlier with no silent type drift (a unit price written as "5.00" versus "5,00" versus "$5.00" matters when the formula column reads it), no merging of multi-line descriptions into a single cell that strips the line break, and no skipped lines on multi-page POs where the page break interrupts a line item.

GRN extraction. Buyer-generated, but the messiest input by a wide margin. GRNs arrive as scanned hand-written goods received vouchers from receiving bays, as warehouse-system exports in inconsistent formats, as photographed delivery notes the receiving team signed and emailed back to AP, or as legacy receiving reports printed and re-scanned. Quantity received, condition flags (damaged, short, rejected), and the link to the PO line are the data points that matter and the ones most often unclear in the source document — a hand-written "10 of 12 OK, 2 damaged" needs to extract as a received quantity of 12, a condition flag, and an explanatory note, not as the literal string. This is the leg that justifies a dedicated extraction step; the companion piece on extracting goods received notes and GRVs from paper, PDF, and scanned receiving paperwork walks the GRN-specific pitfalls in depth.

Invoice extraction. Supplier-generated, post-delivery. Format variance across suppliers is the dominant challenge: each supplier's PDF layout is different, each carries the supplier's own line numbering and item descriptions, and tax treatment varies by jurisdiction (a VAT-registered supplier in the UK, a sales-tax supplier in the US, and a GST supplier in Australia all bill differently). The invoice columns from the schema — invoice number, invoice date, line quantity billed, line unit price, line total, tax amount — must extract correctly per supplier. The broader topic of supplier invoice extraction, including the supplier-by-supplier variance work, sits across the rest of the invoice-data-extraction cluster.

Services parallel. For service categories — consulting work, maintenance contracts, professional services — the GRN is replaced by a Service Entry Sheet recording the service delivered, the period, and the approver. The same schema applies line-for-line, with the SES sitting in the role the GRN plays for goods. The dedicated coverage of the service entry sheet (SES) parallel to GRN for services picks up the services-specific shape.

Partial deliveries and progressive billing. One PO line frequently maps to multiple GRN events (split delivery across days or warehouses) and multiple invoice events (progressive billing, where the supplier invoices each shipment separately or invoices a percentage of the contract per milestone). The long-format schema absorbs this by adding explicit receipt-event and invoice-event keys to each row, so the worked example from the line-key section extends naturally to many-to-many maps without restructuring the columns. The full treatment of reconciling one PO across multiple GRNs and progressive invoices sits in its own piece — the point here is that the schema was designed to absorb the complication, not collapse under it.

Exception Triage — What Happens After the Spreadsheet Is Built

The spreadsheet's whole purpose is to surface exceptions; triage is the human workflow that resolves them. Each named exception code has a known owner and a known next action, and the AP workflow for closing three-way match exceptions falls into four routing groups by who acts.

Supplier-facing exceptions. Price variance, duplicate invoice, currency mismatch, tax-rate mismatch, and line-not-on-PO all go back to the supplier. The conversation is for a credit note, a corrected invoice, or a documented explanation that AP can attach to the row before posting. The AP clerk holds the row open until the supplier responds, and the supplier's own invoice line number — which the schema preserves alongside the buyer's — is what the clerk quotes in the dispute. Price variance is the highest-volume code in this group at most businesses, and it is where consistent practice on tolerance documentation pays off; the supplier will ask what threshold they tripped, and the answer needs to come from the row.

Receiver-facing exceptions. Quantity short and quantity over go to the receiving team or warehouse for a documented condition note. Quantity short often resolves into a damaged-or-rejected condition flag on the GRN that AP did not originally see — the receiving team accepted partial goods on day one and the missing portion was a returned-to-supplier transaction that never wrote back to the goods received note. Quantity over usually resolves into either an unauthorized over-shipment the receiver accepted in good faith (which then becomes either a returned-to-supplier or a negotiated extra-quantity acceptance) or a unit-of-measure question that should have been caught by the composite key.

Procurement-facing exceptions. Missing GRN (when an invoice is present without a recorded receipt) and wrong supplier go to procurement. Procurement either confirms the receipt happened off-system — paperwork lost, GRN not posted, goods delivered to a project site without a receiving step — and posts the GRN retrospectively; or confirms the PO was misrouted and reissues it under the correct supplier. The wrong-supplier case is where invoice fraud most often surfaces, and procurement is the function that owns supplier identity.

Finance-facing exceptions. Missing invoice is the one code that does not chase the supplier; it accrues. The accrual side of accounts payable books a GRNI entry at month-end for goods received but not yet billed, and clears the accrual when the supplier invoice arrives. The accounting mechanics — debit, credit, reversal timing, audit posture — sit in the companion piece on GRNI accruals for goods received but not yet invoiced.

The controller's view of the three-way reconciliation queue is different from the AP clerk's. The controller works at the queue level, not the row level. They look at exception counts by code over time (is price-variance volume rising and what suppliers are driving it?), exception aging (how many rows are open more than 30 days, and is the aging concentrated in any one supplier or category?), and exception concentration by supplier (one supplier driving 40% of the variance count signals a relationship issue, not a one-off variance). The schema's columns — supplier code, exception code, exception aging derivable from the date columns — make these queue-level views buildable directly from the comparison spreadsheet without rebuilding the data into a separate reporting layer.

A specific failure-mode worth flagging separately: AP clerks who repeatedly see lines that "should match but don't" are usually looking at structural problems in how the three documents' line data extracts and joins, not at one-off exceptions. The deeper coverage of line-level failure modes when invoice lines do not align with PO lines walks the specific patterns — line consolidation, line splitting, line re-ordering, UoM-conversion artifacts — that drive that "should match but doesn't" feeling.

The discipline of triage closure matters as much as the triage itself. An exception is not closed when it is explained; it is closed when the row's match flags read clean (corrected invoice posted, credit note applied, GRN updated, accrual booked) or when the row is escalated and recorded as accepted variance with a documented reason and an approver. The spreadsheet is the audit trail — a controller or auditor reading it later needs to see what was wrong, what was done about it, and who signed off.

Tooling Fit — Excel, AP Automation, or Extraction Plus Spreadsheet

Three honest options for running this workflow, and the right answer depends on the volume, the supplier mix, and the ERP integration shape — not on which option markets itself best.

Excel only. Works for low-volume operations, roughly under 50 POs per month before the spreadsheet maintenance overwhelms the matching value. Power Query plus XLOOKUP (or VLOOKUP for legacy versions) gets the reader to a basic match on the composite key — the source columns from each document feed three named ranges, the composite key joins them, and computed columns produce the flags. Excel-only three-way match workflows of this shape genuinely work for a small enough business and should not be apologised for. Where they fall over: line-key disambiguation across a supplier base of more than 20 or 30 active suppliers, where the volume of UoM and sequence edge cases starts breaking formulas faster than they can be patched; speed at month-end close when the queue spikes from a hundred rows to a thousand and the workbook recalculates for ten minutes after every change; and the audit trail, because formula columns are easy to break and the broken state is easy to miss. The Excel-only shape is where three-way matching without ERP most often lives, and for the volumes that fit, it is the right answer.

AP automation tool with built-in three-way match. Works when the tool ingests all three document types cleanly, when the ERP integration is solid (PO data flowing in from procurement, GRN data flowing in from receiving, supplier-master maintained), and when the supplier mix is concentrated enough that supplier-format variance is tractable for whatever extraction layer the tool uses. Falls over when supplier formats are heterogeneous — a long tail of small suppliers, each with a different invoice layout, swamps any built-in template library; when the ERP does not feed POs cleanly because procurement runs outside the unified system; or when the matching policy needs frequent adjustment that the tool's configuration screens do not support without a vendor change request. This shape is the right answer for a mid-market or enterprise AP team with a tight supplier mix and a clean ERP feed.

Extraction tool plus spreadsheet. The middle ground, and the shape this article opened by reframing. The extraction step handles the data-preparation problem — converting POs, GRNs, and supplier invoices from their native formats into the comparison schema — and the matching logic then runs in the spreadsheet, in a BI tool, or in the ERP's matching engine if one exists. The AP team keeps control of the matching policy, the tolerance windows, and the exception-code taxonomy; the data prep stops being a manual exercise without forcing the team into a full AP automation platform with all the supplier-format and ERP-integration constraints that brings.

This is the shape our product is built for: extract PO, GRN, and invoice data into the comparison schema automatically, in the columns and key structure this article named, from whatever PDFs and images the three documents arrive as — native PDFs, scanned PDFs, and photo or scan images of paper receiving paperwork. The AP team describes the comparison columns they need in a prompt, uploads a batch of POs, GRNs, and invoices, and gets back a structured spreadsheet keyed by PO number, PO line, supplier code, item code, and unit of measure. A goal-oriented prompt — "I'm reconciling supplier invoices against purchase orders and delivery notes for three-way matching, with one row per PO line, item code, and unit of measure" — handles a mixed batch in the same shape every run, which is what makes the comparison schema reliable enough to run matching logic on top of. The matching policy, the tolerance windows, and the exception triage all stay with the AP team.

The invoiceless alternative. Some supplier categories bypass the three-way match entirely through evaluated receipt settlement as the invoiceless alternative to three-way matching, where the buyer pays against the PO and GRN without waiting for a supplier invoice — typically used for high-trust, high-volume supplier relationships where invoice-by-invoice reconciliation costs more than it catches. Worth knowing about as a workflow choice, even if it does not fit most supplier relationships. Not every category needs to stay a three-way match.

The tooling decision follows the workflow shape, not the other way around. The right answer is the one that fits the volume, the supplier mix, and the audit posture the AP team is accountable for.