How to compare two supplier price lists in Excel
Two price lists from the same supplier — the one you have been buying against and a newer one — and the job is to find what changed between them in Excel: match the items by SKU or item code, work out the absolute and percentage change on each cost, and pull out the items that are new, that were removed, or that did not match. This is a before-and-after comparison of one supplier's own list, not a comparison of one supplier against another to find the lowest price. Prefer to skip building the lookups yourself? CostRift runs the same comparison in one step.
What you need before you start
- The previous supplier price list and the current one, from the same supplier, as CSV or XLSX.
- Excel, or a spreadsheet with the same lookup functions — XLOOKUP or VLOOKUP, and optionally Power Query.
- An item identifier column — a SKU or supplier item code — in each file.
- A unit cost column in each file.
- Optionally, your selling prices, if you also want the margin impact; and currency or unit-of-measure columns if the files carry them.
You do not need every column the supplier sends. A SKU and a cost in each file is enough for the comparison itself.
The method in Excel, step by step
Set up the previous and current price lists as two sheets
Open one workbook with two sheets: Previous for the list you have been buying against and Current for the new one from the same supplier. Give each sheet a header row and keep the columns you need in a known place — an item identifier (SKU or supplier item code) and a unit cost in each, plus your selling price if you want to look at margin later, and currency or unit of measure if the files carry them. On the current sheet, the helper columns you will build are Previous Cost, Change, Change % and Status.
Match items by their identifier, exactly first
Pair a row in Current with a row in Previous by the item identifier, matched exactly as written. Do not use the product description as the key — two differently coded products can share a description, and the same product can be described two ways. Where the codes look like they should match but do not — a stray space, a different letter case — inspect the difference before you act on it.
Excel's TRIM and UPPER can standardise spacing and letter case in a helper column if you have decided two codes are meant to be the same item. Treat that as your own clean-up choice — a practical way to standardise formatting — not a guaranteed match, and not the same normalization CostRift applies. CostRift trims only the outer whitespace of an identifier, compares ASCII letters case-insensitively, leaves non-ASCII characters as they are, rejects empty or control-character codes, and still holds duplicates and collisions back for review rather than pairing them. Whatever you clean up, never force a genuinely ambiguous code to match just to get a number.
Bring the previous cost next to each current row
On the Current sheet, fill the Previous Cost column by looking the value up from the Previous sheet by identifier, using an exact match.
=XLOOKUP([@SKU], Previous[SKU], Previous[Previous Cost], NA())
XLOOKUP uses an exact match by default; do not switch it to an approximate or wildcard mode for identifiers, which would pair codes that merely look similar. On older Excel without XLOOKUP, VLOOKUP with a FALSE last argument does the same exact-match lookup:
=VLOOKUP([@SKU], Previous!A:C, 3, FALSE)
A row that returns #N/A has no counterpart in the previous list — but read it before you label it. A miss can also mean a formatting difference, a mistyped identifier, or a code that appears more than once.
Calculate the absolute and percentage price change
Add a Change column for the absolute cost movement and a Change % column for the proportional one.
absolute change = new cost − previous cost
=[@[New Cost]]-[@[Previous Cost]]
A positive result is a cost increase, a negative one a decrease, and zero is unchanged.
percentage change = (new cost − previous cost) ÷ previous cost
Guard the percentage against a previous cost of zero, where the proportional change is undefined — leave it blank rather than letting the sheet show a divide-by-zero error or an infinity:
=IF([@[Previous Cost]]=0, "", ([@[New Cost]]-[@[Previous Cost]])/[@[Previous Cost]])
Find the new products
An identifier in Current with no valid match in Previous is a candidate new item. The spreadsheet signal is the #N/A from the exact lookup, which you can turn into a flag:
=IF(ISNA([@[Previous Cost]]), "New", "")
Confirm a sample against the supplier before you treat the list as final: a #N/A can also be a formatting difference or a bad identifier, and a genuinely new code is sometimes just a re-code of an item that is still being supplied.
Find the products that were removed
Run the lookup the other way. On the Previous sheet, look each identifier up in Current; anything that returns #N/A is in the previous list only and is a candidate for removal from the current price list.
=IF(ISNA(XLOOKUP([@SKU], Current[SKU], Current[New Cost], NA())), "Removed", "")
Missing from the new file is not the same as discontinued by the supplier — it can be a re-code, an omission, or a range change. Check the ones that matter.
Review the codes that did not match cleanly
Some rows will not resolve to a clean pair: a duplicated identifier, a blank one, a code that only matches after a clean-up you are not sure about, or a lookup miss you have not explained. Put these in a review list rather than taking the first match or the closest description. An arbitrary pairing produces a confident but wrong change figure. CostRift takes the same position — a duplicate identifier or a normalized-form collision is left unresolved, not guessed.
Filter to the rows where the cost increased
Sort or filter the Change or Change % column to bring the increases to the top — Change > 0 — and work through those first. Conditional formatting can shade the increases as well, but keep the filter as the primary way in so nothing depends on colour alone.
Power Query for a comparison you repeat
If the same supplier sends a new list every quarter, rebuilding lookups each time is where effort and mistakes accumulate. Power Query keeps the steps and re-runs them on a fresh file.
- Load the previous and current lists as two queries (From Table/Range, or from the source files).
- Merge Queries on the identifier column to bring the previous and current cost into one table.
- A left-anti join keeps the rows that are in the previous list only — the removed items. A right-anti join keeps the rows that are in the current list only — the new items.
- Add the change and percentage columns as custom columns, then refresh when the next file arrives.
The menu wording and the join names vary between Excel versions and Power BI; the shape of the steps is the same.
Where a spreadsheet gets harder to maintain
A spreadsheet handles this comparison well when it is occasional, the file layout is stable between versions, the number of rows is manageable, and whoever runs it understands the lookup logic.
It gets more manual when the supplier changes the column layout between files, when a few hundred rows silently fail to match, when duplicated identifiers need judgement one by one, when the comparison comes round often, or when you also need the margin impact of each change alongside the cost movement. None of that makes a spreadsheet wrong — it is a question of how much repeated hand-work you want to own.
Compare the files in one step instead
CostRift runs this same comparison without the lookups. You map the SKU and cost columns in each file — CSV or XLSX, mapped independently so the column names can differ — and it matches items by identifier: exactly as written first, then by a normalized form that trims outer whitespace and ignores ASCII letter case. Every matched row is classified as increased, decreased or unchanged with the absolute and percentage cost change; unmatched rows become new or removed; rows it cannot pair cleanly are held back as unresolved; and a matched row whose currency or unit of measure differs between the lists is flagged as incompatible rather than compared.
Files up to 16 MiB and 100,000 rows are read and compared in your browser and are not uploaded to CostRift servers to run the comparison; structured rows are stored only when you are signed in and choose Save to History. If you also map your selling prices, each changed row shows the effect on your gross margin and the selling price that would hold your previous margin. CostRift does not convert currencies or units of measure, does not use product descriptions to pair items, and does not change any prices for you — it produces the figures. The Privacy Policy covers how files and saved rows are handled.
A worked example
Seven items from one supplier, previous cost against current cost, with the change, the percentage, and the classification. Numbers are illustrative and in any currency.
| Item | Previous | Current | Change | % | Status |
|---|---|---|---|---|---|
| A-100 | 10.00 | 11.50 | +1.50 | +15% | Increased |
| A-205 | 8.00 | 7.20 | −0.80 | −10% | Decreased |
| A-330 | 4.50 | 4.50 | 0.00 | 0% | Unchanged |
| A-540 | — | 22.00 | — | — | New |
| A-110 | 6.00 | — | — | — | Removed |
| A-777 | 12.00 USD | 12.60 CAD | — | — | Review (currency differs) |
| A-820 | 9.00 | 2 rows | — | — | Review (duplicate code) |
A-540 is in the current list only, so it is new; A-110 is in the previous list only, so it is removed; neither has a before-and-after cost. A-777 is quoted in a different currency in each file, so the raw numbers are not comparable and the row is left for review rather than differenced. A-820 appears twice in the current list, so there is no single row to pair — it is reviewed by hand, not matched to whichever line comes first.
Frequently asked questions
How do I compare two supplier price lists in Excel?
Put both lists in one workbook, one sheet each. On the current sheet, look the previous cost up by SKU or item code with an exact-match XLOOKUP or VLOOKUP, then add a column for new cost minus previous cost and another for that divided by the previous cost. Flag the lookup misses as new items, run the lookup the other way for removed items, and filter the change column to the increases.
Should I use XLOOKUP or VLOOKUP for price-list comparison?
Use XLOOKUP if your Excel has it: it looks left or right, takes an exact match by default, and returns a value you choose — such as NA() — when there is no match. VLOOKUP with FALSE as the last argument does the same exact-match lookup on older versions, but the key column has to sit to the left of the value you want back.
How do I calculate the percentage price change in Excel?
Divide the absolute change by the previous cost: (new cost − previous cost) ÷ previous cost, formatted as a percentage. Wrap it in an IF that returns a blank when the previous cost is zero, because the proportional change is undefined there rather than infinite.
How do I find new products in an updated supplier price list?
A SKU in the new list with no exact match in the previous list is a candidate new item; the spreadsheet signal is the #N/A from the lookup. Check a sample with the supplier, because a #N/A can also be a formatting difference, a typo, or a re-code of an item that is still supplied.
How do I find products that were removed from a supplier price list?
Run the lookup in reverse — each previous SKU searched in the new list — and treat the #N/A results as items in the previous list only. Missing from the new file is a candidate for removal, not proof the supplier has discontinued the item; confirm the ones that matter.
What should I do when an SKU does not match?
Inspect it rather than force it. Look for a stray space or a letter-case difference and, if you are sure two codes are the same item, standardise them in a helper column. If a code is duplicated, blank, or genuinely ambiguous, put the row in a review list instead of pairing it with the closest-looking line.
When should I use Power Query instead of formulas?
When you run the same comparison repeatedly. Power Query records the load, merge and anti-join steps once and re-runs them on each new file with a refresh, which removes the per-round rebuild that formula-based sheets need. For a one-off comparison, formulas are quicker to set up.
Where to go next
For the same comparison across a whole price list without the lookups, see CostRift's supplier price list comparison. For the method on its own terms — by hand or with any tool — see how to compare two supplier price lists. When the point of the comparison is a cost increase, how to analyze a supplier price increase and responding to a supplier price increase go into the margin maths and the decision that follows. If the question is instead which of several suppliers is cheapest for the same items, that is a different comparison — see comparing supplier quotes or, for the same method in Excel, comparing supplier quotes in Excel.
Compare your two price lists
Bring a previous and a current supplier price list — CSV or XLSX — and get every change, new item, and removed item in one pass.
Compare supplier price lists