Guide
How to compare two supplier price lists in Excel
You have last quarter's price list and the one the supplier just sent. You want three things: which prices changed and by how much, which products are new, and which products are no longer listed. Here is a method that works in Excel with a lookup formula, the checks to run first so the matches are trustworthy, and where the method breaks down.
Before you match anything: three checks
A lookup pairs rows by product code. If the codes are damaged, every result after that is wrong, so check the codes first.
- Leading zeros. If codes in one file are 00123 and in the other 123, the lookup will find nothing. Both files need the codes as text. See how to keep leading zeros.
- Duplicates. If a code appears twice in the new list, a lookup returns the first one it finds and says nothing about the second. Find duplicates before matching. See how to find duplicate product codes.
- Stray spaces. "A100 " and "A100" are different to Excel. Wrap the lookup value in TRIM, or clean the column first with a TRIM formula copied over the codes.
Set up the two sheets
Put the old list on a sheet called Old and the new list on a sheet called New, each with the product code in column A and the cost price in column B, one header row, and no blank rows inside the data. If the supplier's file has extra columns, that is fine; the formulas below refer to whole columns.
Step 1: bring the new price next to the old one
On the Old sheet, in cell C2, enter:
=XLOOKUP(TRIM(A2), New!A:A, New!B:B, "not in new list", 0)Copy it down. The fourth argument is what XLOOKUP returns when there is no match, so instead of #N/A you get readable text. The fifth argument, 0, means exact match, which is what you want for codes. XLOOKUP is in Microsoft 365, Excel 2024 and Excel 2021. It is not in Excel 2019 or 2016; use VLOOKUP there:
=IFERROR(VLOOKUP(TRIM(A2), New!A:B, 2, FALSE), "not in new list")The FALSE at the end matters. VLOOKUP's default is an approximate match, which on an unsorted list of codes silently returns wrong rows.
Step 2: the change and the percentage
In D2, the change in money:
=IF(ISNUMBER(C2), C2-B2, "")In E2, the change as a percentage of the old price:
=IF(AND(ISNUMBER(C2), B2<>0), (C2-B2)/B2, "")Format E as a percentage. The B2<>0 test stops a divide-by-zero error on free items. Sort or filter on D to see the increases at the top.
Step 3: products that are new
Do the lookup in the other direction. On the New sheet, in C2:
=XLOOKUP(TRIM(A2), Old!A:A, Old!B:B, "new product", 0)Filter column C for "new product". These codes are in the new file only.
One warning. A code that is in the old list and not in the new one is not necessarily discontinued. Suppliers send partial lists, seasonal lists and lists for one category. Treat "not in new list" as a question for the supplier, not a fact.
Step 4: the things a lookup cannot see
A lookup compares one number to another number. It does not know that:
- the pack size changed from 12 to 24, so a price that "went up 80%" is really a price cut per unit;
- the currency column changed from USD to EUR;
- a price cell says "n/a", "call" or "TBC", which Excel treats as text and your subtraction turns into #VALUE!;
- a price was entered as 12,50 in one file and 12.50 in the other, so one of them is text.
If your lists have pack sizes or currencies, add a column that compares them too, for example =IF(F2=XLOOKUP(TRIM(A2), New!A:A, New!F:F, "", 0), "", "pack changed"), and review every flagged row before you trust the price change.
What about Excel's Compare Files?
Excel has a Spreadsheet Compare tool, opened from the Inquire tab. Microsoft's documentation says it is available only in Excel for Windows in Microsoft 365 Apps for enterprise and equivalent editions, so most small businesses on Business or Personal plans do not have it. It also compares cell by cell in position, which is the wrong question when the supplier has reordered the rows.
When the spreadsheet method is enough, and when it is not
For one supplier, a few hundred products, and a list you trust, the formulas above are fine and take ten minutes once set up. They start to fail when you have several suppliers with different layouts, thousands of rows, codes that are not clean, or pack and currency columns that need checking every time.
The price list checker on this site does all of the above in one pass: it matches by code with leading zeros and case kept, refuses to guess on duplicates and blank codes, flags pack size and currency changes and unreadable prices instead of comparing them, and gives you an .xlsx report with the source row numbers from both files. Your files are compared in your browser and never uploaded. It is free during the beta.