How to compare supplier price lists

Pair the old and new rows by the supplier's item code, turn both prices into a cost per unit, then sort every row into increase, decrease, unchanged, new or dropped. A plain lookup gets most rows right. The rows it gets wrong are the ones that cost money.

Updated 29 September 2026 · Proceny

The method in six steps

Put both lists in one workbook, one sheet each (Old, New). Keep the supplier's files unchanged.
Choose the key: the supplier's item code. If the supplier renumbered its items, use the manufacturer part number (MPN) or GTIN instead.
Clean the key the same way in both sheets: text, no spaces, no dashes, one case.
Bring the old price, unit, price basis and pack quantity next to each new row.
Turn both prices into a cost per unit before you compare them.
Classify each row, and set aside every row where the unit, pack, price basis or currency changed — those need a person, not a formula.

In Excel

Add a helper column Key to both sheets. The examples assume the key is in column F, the price in E, the price basis (1, 100 or 1,000) in G and the pack quantity in H.

Key            =UPPER(SUBSTITUTE(SUBSTITUTE(TRIM(A2)," ",""),"-",""))
Old price      =XLOOKUP($F2, Old!$F:$F, Old!$E:$E, "NEW")
Duplicate?     =COUNTIF($F:$F, $F2) > 1
Unit cost      =E2 / (G2 * H2)
Change         =IF(ISNUMBER(OldUnit), NewUnit / OldUnit - 1, "")
Dropped?       =ISNA(XMATCH(F2, New!$F:$F))       (on the Old sheet)

OldUnit and NewUnit are the two unit-cost columns. Without XLOOKUP, use =IFERROR(INDEX(Old!$E:$E, MATCH($F2, Old!$F:$F, 0)), "NEW"). If you use VLOOKUP, the key must be left of the price and the last argument must be FALSE: without it VLOOKUP does an approximate match and can return a neighbouring item's price without any error. With tens of thousands of rows, Power Query's Merge Queries (a full outer join on the key) does the same pairing and shows new and dropped rows in one table.

Where a lookup goes wrong

1. The supplier changed its item codes

The lookup reports the item as new, and the old code as dropped. Before you accept either, look for new and dropped rows with the same manufacturer part number, GTIN or description, and pair them by hand or by that column.

2. Excel damaged the codes

Opening a CSV turns 00123 into 123, a 13-digit GTIN into 1.23457E+12 and 3-12 into a date. The damaged code matches nothing, or the wrong thing. Import code columns as text (Data → From Text/CSV, column type Text) and compare the row counts with the supplier's file.

3. Duplicate codes

The same code twice — in two colours, two pack sizes, or simply repeated — and a lookup quietly returns the first one. Count every key in both lists and resolve duplicates before comparing.

4. The pack size changed

Code	List	Unit	Price	Per unit
A-4410	Old	Box of 12	48.00	4.00
A-4410	New	Box of 10	44.00	4.40

The box price fell 8.3 %. The price per piece rose 10 %. Compare per unit, and treat any change in pack quantity as a question for the supplier, not only a new number.

5. The price basis changed

Last list: 62.00 per 100 pieces. New list: 0.65 each. Compared as printed, that is a 99 % decrease; per piece it is 0.62 → 0.65, an increase of 4.8 %. A basis can sit in its own column (often E, C or M for each, per 100 and per 1,000), in the header (“Price/100”) or in the price cell itself. How to handle per-100 prices and pack changes

6. Different units

Cable per foot in one list and per metre in the next, or a roll of 100 m replacing a price per metre. Convert both to one unit with a fixed factor (1 ft = 0.3048 m). Each and kilogram cannot be converted without a weight, so do not force it.

7. New items

Rows only in the new list are not increases. They need a cost set up in your system, and often a check that they are not an old item under a new code (case 1).

8. Dropped and discontinued items

Rows only in the old list are easy to miss, because a lookup starts from the new list. Run the check from the old side too. Some suppliers keep discontinued items in the list with a status or a note instead of removing them; filter on that column.

9. Another currency

A list re-issued in another currency makes every row look changed. Do not compare until you have decided which exchange rate applies, and write down the rate you used.

Why the raw percentage misleads

A percentage on a box price is not a percentage on what you pay per piece (case 4).
On small prices, tiny moves look large: 0.02 → 0.03 is +50 %. Sort by the change in money on the quantities you actually buy, not by percentage alone.
One basis error (+9,900 %) wrecks an average. Report the typical change — the middle value, the median — and list the largest changes separately.
Treat any change beyond about ±40 % as a unit, pack or basis question first. It usually is one.

Checklist before you update costs

Row counts match the supplier's files; code columns were imported as text.
No duplicate keys left unexplained.
Every row compared per unit, in one currency.
Rows with a changed pack, unit or price basis checked with the supplier.
New and dropped rows reviewed from both sides, renumbered items paired.
The largest changes in money looked at one by one.