How to Match Purchase Orders and Invoices in Excel—with a Free Template

To match a purchase order and invoice in Excel, put the records in separate tables, create a consistent item key, check whether each key is unique and compare the relevant quantities, units and prices. Check in both directions: invoice rows without a PO match and PO rows absent from the invoice.

The downloadable workbook follows that approach. It includes a complete fictional example and deliberately sends ambiguous cases to manual review.

Download the free Excel matching template. No email is required. Download the source PDFs to follow the example.

What the template does—and what it leaves to you

The workbook is designed for one invoice against one purchase order, from the same supplier, in one currency. It accommodates up to 100 data rows on each input sheet, in rows 7–106. It uses formulas without macros or external data connections.

It checks the entered references and currency against the settings you provide, tests simple line arithmetic, identifies repeated keys, compares eligible one-to-one lines and highlights PO items absent from this invoice.

It does not read PDFs, verify supplier identity, check receiving records, consult payment history or manage cumulative billing across multiple invoices. It does not automatically normalize units, allocate partial deliveries, interpret discounts or process credit notes. Every proposed result requires source review.

The workbook requires an Excel version with XLOOKUP, such as Microsoft 365 or Excel 2021/2024; XLOOKUP is unavailable in Excel 2016/2019 according to Microsoft. The formulas are checked during file generation, but this package has not been interactively tested in every Excel desktop, web or third-party spreadsheet version. Open the workbook in Excel, confirm recalculation is enabled and check the supplied sample totals before replacing the data.

The six worksheets

Sheet	Purpose	What you edit
Start	Instructions, scope settings and summary totals	Expected PO, invoice, currency and arithmetic tolerance
PO	Original purchase-order values and source locations	Input columns A–I
Invoice	Original invoice values and source locations	Input columns A–J
Review	Formula-based invoice-to-PO checks	Decision column R only
PO_Coverage	Check for PO items absent from this invoice	Formula sheet; do not overwrite
Discrepancies	A practical follow-up log	Review notes, owner, status and resolution; preserve sample delta formulas until replacing them intentionally

Input cells are visually distinguished from calculated columns. Keep a clean copy of the workbook before starting a new review.

Step 1: verify the documents and set the scope

Open the original PO and invoice. Confirm that the supplier and buyer are the expected parties and that the invoice references the correct PO. Identify the revision that your reviewer has authorized as the comparison basis.

On Start, set B8 to the expected PO number, B9 to the invoice number and B10 to the currency. B11 contains a money tolerance of $0.01 in the sample. That value controls arithmetic and unit-price flags; it does not authorize payment or establish your company’s commercial tolerance.

The sample uses PO-1042 and INV-8821 in USD. The expected source totals are $1,640.00 for the PO and $1,512.00 for the invoice, a −$128.00 difference. Those totals merely confirm that the sample was loaded; they do not show that every item is correct.

Step 2: enter every source row

On PO, enter the PO number, SKU, description, quantity, unit, unit price, currency, stated line amount and source location. On Invoice, enter the equivalent invoice information, including the invoice number and referenced PO number.

Keep source amounts as printed. The workbook separately calculates quantity multiplied by unit price so that a disagreement with the stated amount remains visible. When a source uses discounts or other line calculations, the simple arithmetic check may require a different method; do not edit the source amount merely to silence a flag.

Give each row a usable location such as PO-1042.pdf p1 line 20. A supplier can respond more easily to a precise line than to an unexplained number.

Include added charges as their own invoice rows. Do not drop freight because it lacks a product code; enter an explicit descriptive identifier such as FREIGHT and review the resulting unmatched item.

For a new review, clear all old input rows and old decisions. Leaving one old row in the range can create a false match or change a total.

Step 3: build the match key

The workbook normalizes the PO number, SKU and currency to uppercase, removes leading and trailing spaces and joins them into a key:

PO-1042|BX-200|USD

The PO key is in column J; the invoice key is in column K. The supplier is not part of the key because the workbook is explicitly limited to one already-verified supplier. Do not combine unrelated suppliers in one file.

The vertical bar is reserved as a separator and must not appear inside a PO number or SKU. Case is not significant in this template. A supplier using case-sensitive identifiers requires another matching design.

Description similarity alone is insufficient for the template to establish identity. When your supplier uses a different SKU, confirm the cross-reference manually rather than changing the original identifier without a record.

Step 4: check uniqueness before retrieving a value

A lookup can produce a plausible answer when a key appears twice. Microsoft’s XLOOKUP documentation explains that its normal search returns the first match. That behavior makes an explicit uniqueness check important.

On Review, column C contains the normalized key. The workbook counts exact key equality with SUMPRODUCT so that characters such as * and ? remain literal identifier characters. Microsoft’s SUMPRODUCT documentation explains its array calculation behavior.

The PO-side count in D7 is:

=SUMPRODUCT(--(PO!$J$7:$J$106=C7))

The invoice-side count in E7 is:

=SUMPRODUCT(--(Invoice!$K$7:$K$106=C7))

Each equality produces TRUE or FALSE; the double minus converts those values to one or zero, and SUMPRODUCT adds them. The downloaded formulas include blank-row guards in addition to the expressions shown here.

When both counts equal one, a one-to-one comparison may be possible. When a key is repeated, review the underlying records before aggregating anything.

Step 5: compare compatible values

For a unique PO key, this lookup retrieves the PO unit price from column F:

=IF(D7=1,XLOOKUP(C7,PO!$J$7:$J$106,PO!$F$7:$F$106,"",0),"")

A safe comparison still needs the same unit and currency, valid source values and an appropriate scope. The workbook leaves calculated differences blank when those requirements are not met.

For an eligible row, quantity difference is invoice quantity minus PO quantity, and unit-price difference is invoice unit price minus PO unit price. The line-amount difference uses the stated source amounts. A blank difference is not zero; it means that this simple comparison was withheld.

In the sample, BX-200 is 50 each on both documents, with a unit price of $7.60 on the invoice and $8.00 on the PO. The unit-price difference is −$0.40 and the line-amount difference is −$20.00.

Step 6: interpret the review flags

Flag	What it means	What to do
INPUT REVIEW	Required input is missing, nonnumeric where a number is needed, negative in this template or uses a reserved separator	Check the original and correct the entered data or use a different method
SCOPE REVIEW	Entered references or currency do not match the selected review	Verify the documents and settings
ARITHMETIC REVIEW	Simple quantity × price differs from the stated amount beyond the tolerance	Inspect the source calculation, discounts and transcription
NO PO MATCH	No matching PO key was found	Investigate an added item, incorrect key or missing document
PAIRING REVIEW	A key is repeated on either side	Inspect split rows, duplicates and one-to-many relationships
UNIT REVIEW	The paired records use different units	Obtain a supported conversion and review it manually
DIFFERENCE	An eligible one-to-one quantity or price differs beyond the relevant rule	Establish the reason and record a decision
NO FLAG - REVIEW REQUIRED	The limited formula checks found no exception	Still verify the source and required approvals

For CX-300, the workbook displays UNIT REVIEW even though the sample PO documents 12 each per case. For DX-400, it displays PAIRING REVIEW because one PO line maps to two invoice rows. Both cases can be explained manually, but automatic conversion and grouping are outside this template’s scope.

Step 7: check the other direction

Open PO_Coverage. EX-500 is marked NOT ON THIS INVOICE because the PO includes it and the supplied invoice does not.

That finding does not establish a shortage. It may involve a partial invoice, later delivery or another billing record. The workbook checks this invoice only; it has no history of earlier or later invoices.

Without this reverse check, reviewing only the invoice rows would never ask about EX-500.

Record the outcome before calling the task finished

Use the decision field and discrepancy log to record what was accepted, corrected or referred for clarification. Attach or reference the supporting reply. Do not overwrite the original input values to make a disagreement disappear.

For supplier communication, use the invoice discrepancy email templates. For the complete sample explanation, read the worked comparison and answer key.

When the spreadsheet stops being the useful part

Track how much time goes into preparing the tables. If most of the work is repeatedly transferring supplier PDFs into Excel, evaluate that step separately from the formulas.

The file-based automation guide explains how to do that without committing to an accounting-system migration. LineRecon early access is focused on the developing document-comparison workflow.