How to compare two price lists
By the Analistable team · Updated · 6 min read
Match the two lists on SKU (or supplier part number). For every SKU in the new list, pull the old price with XLOOKUP and calculate the change and % change; SKUs not found are new. SKUs in the old list but not the new one are discontinued. The same join answers “which supplier is cheapest?” and “which products now sell below our margin?”.
Part of our guide: How to compare two spreadsheets for differences and matches
Old vs new
Old price =XLOOKUP([@SKU], Old[SKU], Old[Price], "NEW")
Change =IF([@[Old price]] = "NEW", "", [@Price] - [@[Old price]])
Change % =IF([@[Old price]] = "NEW", "", [@Change] / [@[Old price]])| SKU | Item | Old price | New price | Change | Change % |
|---|---|---|---|---|---|
| BOX-S | Small box | 0.42 | 0.42 | 0 | 0% |
| BOX-M | Medium box | 0.55 | 0.61 | 0.06 | 11% |
| TAPE | Packing tape | 1.8 | 1.71 | -0.09 | -5% |
| BAG-L | Large mailer | NEW | 0.38 |
Discontinued items: =FILTER(Old[SKU], COUNTIF(New[SKU], Old[SKU]) = 0, "None").
Two suppliers side by side
Supplier B =XLOOKUP([@SKU], SupplierB[SKU], SupplierB[Price], "")
Cheapest =IF([@[Supplier B]] = "", "A only", IF([@[Supplier A]] <= [@[Supplier B]], "A", "B"))Suppliers often use their own codes. Build a mapping table (your SKU ↔ supplier code) once, then look up through it.
Your selling prices vs new costs
Join your shop's product export to the new supplier list and recalculate margin: =([@[Sell price]] - [@[New cost]]) / [@[Sell price]]. Filter products whose margin dropped below your target — those need a price rise or a new supplier.
Pitfalls
- Prices including VAT in one list and excluding it in the other.
- Different units (price per box of 100 vs per item). Convert before comparing.
- Currency differences between suppliers — convert with the rate on the price list date.
- SKUs with leading zeros turned into numbers by Excel; keep SKU columns as text.
The general technique — matching two lists on a key — is covered in finding matches between two lists.
In Google Sheets
=ARRAYFORMULA(IF(A2:A = "", , IFERROR(B2:B / VLOOKUP(A2:A, Old!A2:B, 2, FALSE) - 1, "NEW")))With SKU and new price in columns A and B, this returns the percentage change against the old list, or NEW for SKUs that weren't on it.
Step by step in Excel
- Put each list on its own tab and format it as a table (Old, New).
- Make the SKU column text on both sides, and TRIM it if the lists came from PDFs or emails.
- In the New table, add Old price, Change and Change % with the formulas above.
- List discontinued SKUs on a separate tab with FILTER.
- Sort by Change % descending to see the largest increases first.
Excel 2019 has no XLOOKUP or FILTER: use =IFERROR(INDEX(Old[Price], MATCH([@SKU], Old[SKU], 0)), "NEW") for the old price, and flag discontinued items with a column in the Old table: =COUNTIF(New[SKU], [@SKU]) = 0. On a Mac, the same formulas work in Excel for Microsoft 365; only some Power Query features differ by version.
Second example: unit and pack-size changes
Suppliers sometimes change the pack size along with the price, which makes the raw comparison misleading. Compare price per unit instead:
Unit price (old) =[@[Old price]] / [@[Old pack]]
Unit price (new) =[@[New price]] / [@[New pack]]
Real change % =[@[Unit price (new)]] / [@[Unit price (old)]] - 1| SKU | Old price | Old pack | New price | New pack | Unit old | Unit new | Real change |
|---|---|---|---|---|---|---|---|
| GLOVE-M | 6 | 100 | 5.4 | 80 | 0.06 | 0.0675 | +12.5% |
| LABEL-A4 | 12 | 500 | 12 | 500 | 0.024 | 0.024 | 0% |
| CABLE-1M | 2.4 | 1 | 4.4 | 2 | 2.4 | 2.2 | −8.3% |
The gloves look 10% cheaper per pack but cost 12.5% more per glove; the cables look 83% dearer but are cheaper per cable. Always compare on the unit you sell or use.
Troubleshooting
| Symptom | Likely cause | Fix |
|---|---|---|
| Every SKU shows NEW | SKU stored as a number in one list and text in the other | Convert both columns to text |
| Some SKUs missing that you know exist | Trailing spaces or different capitals | TRIM both sides; XLOOKUP ignores case |
| Huge % changes | Prices in pence in one list and pounds in the other, or pack size changed | Check units and pack sizes |
| Same SKU listed twice | Variants or price breaks on separate rows | Compare at one quantity break, or add the break to the key |
| Prices read as text | Currency symbols or commas imported as text | Use VALUE or Text to Columns to convert |
Checking and repeating
To check the result, the number of matched SKUs plus new SKUs should equal the row count of the new list, and matched plus discontinued should equal the old list. Recalculate a couple of percentages by hand. When the next price list arrives, replace the New table, move the current one to Old, and every formula updates. For a quick side-by-side without formulas, the compare spreadsheets tool works in your browser; the wider method is in the comparing spreadsheets guide.
Which method when
| Situation | Best method | Why |
|---|---|---|
| One-off check of a few hundred SKUs | XLOOKUP or INDEX/MATCH in Excel | Quick, and easy to share with buyers |
| Monthly list from the same supplier | Power Query merge with a left outer join | Refresh replaces the file without rebuilding formulas |
| Several suppliers with their own codes | Mapping table plus lookups | One place to maintain supplier codes |
| Tens of thousands of SKUs | SQL or Power Query | Formulas become slow at that size |
In Power Query, load both lists, use Merge Queries on SKU with a left outer join from the new list, expand the old price, and add a custom column for the change. A left anti join from the old list to the new list returns the discontinued items — see Power Query merge join kinds.
Common mistakes
- Comparing a list with volume breaks against one without, so the quantity behind each price differs.
- Overwriting the old list before comparing, leaving nothing to compare with — save each version with its date.
- Reading a 0% change as unchanged when the description or pack size changed under the same SKU.
Summarising the impact
Buyers usually want one number: what the new list costs you. Multiply each SKU's change by the quantity you bought last year and sum it: =SUMPRODUCT(New[Change], New[Annual qty]), ignoring new items. This weights a 2% rise on a best-seller above a 20% rise on something you order once a year, and gives you a figure to negotiate with.
Frequently asked questions
- How do I compare two price lists in Excel?
- Use XLOOKUP on the SKU to bring the old price next to the new one, then calculate the change and percentage change.
- How do I find new and discontinued items?
- SKUs with no match in the old list are new; SKUs with a COUNTIF of zero in the new list are discontinued.
- How do I find the cheapest supplier for each item?
- Put each supplier's price next to your SKU list with XLOOKUP and use MIN or an IF to pick the lowest.
- How do I highlight price increases?
- Add a conditional formatting rule on the Change % column, for example a red fill where the value is greater than 0.
- How do I compare price lists with different pack sizes?
- Divide each price by its pack quantity and compare the unit prices, not the pack prices.
- Can I compare price lists in Excel 2019?
- Yes. Use INDEX and MATCH instead of XLOOKUP, and a COUNTIF column instead of FILTER to find discontinued items.
- How do I compare prices in different currencies?
- Convert both lists to one currency using the exchange rate on each price list's date, and note the rate used, so a currency move isn't mistaken for a supplier price change.