Purchase orders and delivery notes: compare them without retyping

A purchase order says what you asked for. A delivery note says what arrived. An invoice says what you are being charged for. Three documents with the same lines, which should agree and often do not: a missing quantity, a changed price, an item billed but never delivered. Comparing them is called three-way matching, and by hand it is exhausting. Here is how to do it in Excel without typing the lines.

Three documents, the same lines

Getting the lines into tables

1. Drop each document onto the PDF to Excel page. On that page the engine concentrates on tables.
2. Check the table in the tables tab: all the lines are there and the columns are right.
3. Export to Excel. Each table opens as its own sheet, with quantities and prices as numbers.
4. Gather the three sheets into one workbook, with short names: Order, Delivery, Invoice.

A table that runs over several pages with the same columns is merged into one sheet on export, with a column showing which page each line came from. Free use reads up to 5 pages per document, and paid pages up to 100.

The comparison in Excel

With the item code in column A of every sheet, quantity in C and price in D, add columns to the invoice sheet that pull data from the other documents:

• Quantity ordered: =XLOOKUP(A2,Order!A:A,Order!C:C,"not ordered")
• Quantity delivered: =XLOOKUP(A2,Delivery!A:A,Delivery!C:C,"not delivered")
• Price on the order: =XLOOKUP(A2,Order!A:A,Order!D:D,"")

Then one flag column: =OR(C2<>F2,D2<>G2), with the delivered quantity in column F and the ordered price in column G. Every row that returns TRUE needs a look. In older Excel versions without XLOOKUP, use VLOOKUP.

What you are looking for

• An item billed but not delivered, or delivered in a smaller quantity.
• An invoice price higher than the order price.
• A partial delivery billed in full.
• An item never ordered that appears on the delivery note.

When there is no item code

Not every supplier prints item codes. Then you match by description, which is less precise: "Screw 4X40" and "Screws 4*40" are the same item, but Excel does not know that. In that case, sort both tables by description and compare them side by side. For future orders, ask the supplier to print item codes.

Worth remembering

Reading extracts what is written. If the supplier wrote a wrong quantity on the delivery note, it comes out as written, and the comparison is what reveals the gap. Lines marked for review are the first to check against the original, before you call the supplier.