How to compare two price lists in Google Sheets
Google Sheets can do the whole comparison with one formula per column that fills itself down, so it keeps working when the next list is longer. Here is the exact setup.
1. Get both lists into one spreadsheet
Tabs named Old and New, SKU in column A, description in B, price in C. Before pasting, select column A on both tabs and choose Format, Number, Plain text, so SKUs like 00123 keep their zeros. If the old list is in another file, pull it in on the Old tab (the first time, click Allow access):
=IMPORTRANGE("URL of the old spreadsheet","Sheet1!A:C")2. Old price for every row, in one formula
In New!D2. ARRAYFORMULA fills the whole column, and IF(A2:A="","", ...) keeps empty rows blank:
=ARRAYFORMULA(IF(A2:A="","",IFERROR(VLOOKUP(A2:A,Old!A:C,3,FALSE),"Not in old list")))XLOOKUP also works in Google Sheets. Put it in D2 and fill down:
=XLOOKUP(A2,Old!A:A,Old!C:C,"Not in old list")IFERROR is what turns the #N/A for a new SKU into readable text. Without it, one new item breaks every SUM over the column.
3. Change and % change
E2 and F2 (format F as percent):
=ARRAYFORMULA(IF(ISNUMBER(D2:D),C2:C-D2:D,""))=ARRAYFORMULA(IF(ISNUMBER(D2:D),C2:C/D2:D-1,""))4. Highlight increases and decreases
Select A2:F on New, then Format, Conditional formatting, Format cells if, Custom formula is. Add one rule per colour:
=AND(ISNUMBER($D2),ROUND($C2-$D2,2)>0)=AND(ISNUMBER($D2),ROUND($C2-$D2,2)<0)5. New and dropped SKUs on their own tab
Items on the old list that are gone from the new one:
=FILTER(Old!A2:C,Old!A2:A<>"",COUNTIF(New!A2:A,Old!A2:A)=0)Items that are new on this list:
=FILTER(New!A2:C,New!A2:A<>"",COUNTIF(Old!A2:A,New!A2:A)=0)Only the lines whose price changed:
=FILTER(New!A2:F,IFERROR(ROUND(New!E2:E,2)<>0,FALSE))Worked example
A cleaning supplies list, five lines each side. The New tab after steps 2 and 3:
| A: SKU | B: Description | C: Price | D: Old price | E: Change | F: % change |
|---|---|---|---|---|---|
| JN-1001 | Trash liners 40-45 gal (case) | $40.90 | $38.50 | +$2.40 | +6.2% |
| JN-1002 | Paper towel rolls (case) | $52.00 | $52.00 | $0.00 | 0.0% |
| JN-1010 | Hand soap 1L | $7.10 | $6.75 | +$0.35 | +5.2% |
| JN-1020 | Glass cleaner (gal) | $8.95 | $9.40 | -$0.45 | -4.8% |
| JN-1040 | Floor finish (gal) | $22.60 | Not in old list |
The dropped-SKU FILTER returns JN-1033 Microfiber cloths (24 pack). The new-SKU FILTER returns the floor finish line.
When a lookup returns "Not in old list" for an item you know is there
The SKUs do not match exactly: a trailing space, a dash, a number stored as a number on one tab and as text on the other. See fixing SKU formatting mismatches, or paste both lists into the free price list compare tool, which normalises SKUs before matching.
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.