The short answer
Put the old list and the new list on two sheets of one workbook. Use the SKU as the key, look up each new SKU in the old list with XLOOKUP, and calculate the change. Then check the other direction, because a lookup from the new list cannot show you what disappeared. For one list that arrives now and then, this free method is all you need.
What you will end up with
Here is a small example. The old list is on a sheet called Old, the new list on a sheet called New. Both have the SKU in column A and the price in column B.
| SKU | Old price | New price | Result |
|---|---|---|---|
| A-100 | 12.00 | 12.00 | Same |
| A-200 | 8.50 | 9.35 | Up 10% |
| A-300 | 20.00 | 16.00 | Down 20% |
| A-500 | none | 7.00 | New SKU |
| A-400 | 5.00 | none | Removed |
Step 1: Clean the SKUs first
Most failed comparisons are not formula problems. Two SKUs that look identical often are not.
- Stray spaces.
A-100andA-100do not match. In a helper column, use=TRIM(A2)and compare that. - Numbers stored as text. A SKU such as
00123can lose its leading zeros when a CSV opens in Excel. Import the file with Data, From Text/CSV and set the column to Text. - Duplicates. If a SKU appears twice, a lookup quietly returns the first match. Count them with
=COUNTIF(A:A,A2)and resolve anything above 1 before you trust the result.
Step 2: Pull the old price into the new list
On the New sheet, put this in C2 and fill it down. It finds the SKU in the old list and returns its price, or the word New if it is not there.
=XLOOKUP(A2,Old!A:A,Old!B:B,"New")XLOOKUP needs Excel 2021, Microsoft 365 or Google Sheets. In older versions of Excel, use this instead:
=IFERROR(VLOOKUP(A2,Old!A:B,2,FALSE),"New")The FALSE matters. Without it, VLOOKUP looks for the nearest match and can return a price from a different product.
Step 3: Calculate the change
In D2, on the same sheet, work out the percentage change. Format the column as a percentage.
=IF(C2="New","New",(B2-C2)/C2)Now sort or filter column D. Look at the largest moves first, in both directions. A price that fell by half deserves as much attention as one that doubled, because it is often a typo or a change of pack size.
Step 4: Find what disappeared
This is the step people forget. A lookup from the new list only sees SKUs in the new list. To find removed items, go to the Old sheet and add this in C2:
=IF(COUNTIF(New!A:A,A2)=0,"Removed","")A removed SKU can mean a discontinued product you are still selling, so it is worth a look even when nothing else changed.
Step 5: Sanity-check the whole file
Before you act on the result, step back and look at the totals.
- Did most prices change at once? Check the currency, or whether the supplier swapped trade and retail columns.
- Did the row count drop sharply? You may have a partial export instead of the full list.
- Do the new prices include tax? A list that changed from excluding to including tax looks like a price rise on every row.
When you have a lot of rows, or you do this every month
The formulas work on thousands of rows, but they need rebuilding for each new file. If you repeat this regularly, Power Query in Excel is worth learning: a merge with a full outer join shows matched, new and removed SKUs in one table, and you can refresh it when the next file arrives.
From there, a price change usually means a decision about your selling price. The markup and margin calculator helps you work out a new price, and if you sell on Shopify, the guide to updating Shopify prices from a supplier CSV covers the import safely.
If you would rather not do this by hand
If your supplier publishes the list as a CSV at a web address, Supplier Price Watch does the comparison every day and emails you the changes, new SKUs and removed SKUs, and warns you when a file looks broken. It is a paid monthly service, so it only makes sense if the list changes often. If your supplier emails a file once a quarter, the formulas above are the better choice.
See the practice files and actual demo result for this product →