How to compare two price lists in Excel
Put the old and new list side by side by SKU, pull the old price into the new list, and let Excel flag what moved. Every formula below is exact; copy it and change the ranges to fit your sheet.
1. Set up the two sheets
Paste the old list onto a sheet named Old and the new one onto New. Make column A the SKU, B the description and C the price on both. If the supplier's columns are in a different order, see matching on SKU when columns are in different orders. If SKUs differ in formatting (AB-100 vs AB100, or lost leading zeros), fix that first: cleaning SKU formatting.
2. Pull the old price into the new list
In New!D2, with Excel 365 or 2021:
=XLOOKUP(A2,Old!$A:$A,Old!$C:$C,"Not in old list")Older Excel, with VLOOKUP. The FALSE matters: without it VLOOKUP does an approximate match and returns a wrong price with no error.
=IFERROR(VLOOKUP(A2,Old!$A:$C,3,FALSE),"Not in old list")INDEX and MATCH does the same and does not care where the price column sits:
=IFERROR(INDEX(Old!$C:$C,MATCH(A2,Old!$A:$A,0)),"Not in old list")3. Change and percentage change
E2, the change in money, and F2, the percentage (format F as %):
=IF(ISNUMBER(D2),C2-D2,"")=IF(ISNUMBER(D2),C2/D2-1,"")4. Highlight what moved
Select A2:F2000 on New, then Home, Conditional Formatting, New Rule, "Use a formula to determine which cells to format". One rule each, with the fill you want. ROUND stops a floating point difference of 0.0000001 counting as a change.
Increases:
=AND(ISNUMBER($D2),ROUND($C2-$D2,2)>0)Decreases:
=AND(ISNUMBER($D2),ROUND($C2-$D2,2)<0)New SKUs, not in the old list:
=AND($A2<>"",NOT(ISNUMBER($D2)))5. SKUs that disappeared
A lookup from the new list can never show an item that is not on it. Go the other way: in Old!D2, fill down
=IF(ISNA(MATCH(A2,New!$A:$A,0)),"Dropped","")More on this, including spotting SKUs that were renamed rather than dropped: finding new and discontinued SKUs.
6. Show only the changed lines
On a third sheet, in Excel 365:
=FILTER(New!A2:F2000,IFERROR(ROUND(New!E2:E2000,2)<>0,FALSE),"No changes")Check for duplicate SKUs before trusting any of this. XLOOKUP and VLOOKUP both return the first match, so a SKU listed twice at two prices gives you one of them silently. In a spare column:
=COUNTIF($A:$A,A2)>1Worked example
Six lines on each list. This is what the New sheet shows after steps 2 and 3:
| A: SKU | B: Description | C: Price | D: Old price | E: Change | F: % change |
|---|---|---|---|---|---|
| AB-100 | Copper elbow 1/2in | $4.45 | $4.20 | +$0.25 | +6.0% |
| AB-101 | Copper elbow 3/4in | $6.10 | $6.10 | $0.00 | 0.0% |
| CT-220 | PVC conduit 10ft | $9.75 | $8.95 | +$0.80 | +8.9% |
| VL-300 | Ball valve 1in | $19.90 | $21.40 | -$1.50 | -7.0% |
| TP-500 | Thread tape | $1.05 | $1.05 | $0.00 | 0.0% |
| FL-610 | Flange 2in | $14.80 | Not in old list |
And step 5 on the Old sheet marks GS-410 as Dropped. Two increases, one decrease, one new SKU, one dropped.
Faster: skip the formulas
Paste both lists into the free price list compare tool. It matches on SKU across renamed columns and formatting differences, and lists increases, decreases, new and dropped items in one go, in your browser.
Pricecheck takes the supplier's email as it arrives, PDF included, compares it with the last version and sends back only the lines that moved. $49/mo, first month free for early signups. No payment now.