How to compare two supplier price lists in Google Sheets

What you need before you start

• The previous supplier price list and the current one, from the same supplier, as a Google Sheet, or a CSV/XLSX you import into one.
• Google Sheets — VLOOKUP and FILTER work in any current Sheets; XLOOKUP and QUERY are both available too.
• An item identifier column — a SKU or supplier item code — in each sheet.
• A unit cost column in each sheet.
• Optionally your selling prices, if you also want the margin impact.

The method in Google Sheets, step by step

1. Set up the previous and current lists as two sheets

   One spreadsheet, two sheets: Previous for the list you've been buying against, and Current for the new one from the same supplier. Each needs a header row, an item identifier column, and a unit cost column. On Current, you'll add Previous Cost, Change, Change % and Status columns.

2. Bring the previous cost across with VLOOKUP or XLOOKUP

   With XLOOKUP, available in current Google Sheets accounts:

   =XLOOKUP(A2, Previous!A:A, Previous!B:B, "")

   XLOOKUP matches exactly by default. Without XLOOKUP, VLOOKUP does the same job, but needs FALSE as its fourth argument — leave it out and Sheets quietly switches to an approximate match, which can pair a SKU with the wrong row instead of returning no match:

   =VLOOKUP(A2, Previous!A:B, 2, FALSE)

3. Calculate the absolute and percentage change

   absolute change = new cost − previous cost

   Guard the percentage against a previous cost of zero, where the proportional change is undefined:

   =IF(C2=0, "", (D2-C2)/C2)

4. Pull out every new item with FILTER, not a dragged-down formula

   FILTER returns every row in a range that meets a condition, spilled into as many rows as actually match — there's no formula to copy down a column. To list every identifier in Current that has no match in Previous:

   =FILTER(Current!A2:A, ISNA(MATCH(Current!A2:A, Previous!A2:A, 0)))

   That single formula spills the full list of candidate new items. Run it the other way — matching Previous identifiers against Current — to get the candidate removed items instead.

5. List the price increases with QUERY, sorted, in one formula

   QUERY runs a pseudo-SQL statement against a range — select, where, and order by, without a separate filter or sort step:

   =QUERY(Current!A1:F, "select A, D, E where D > 0 order by D desc", 1)

   That returns the item, the absolute change, and the percentage change for every row with a cost increase, highest increase first.

6. Check for duplicate SKUs before trusting a lookup

   VLOOKUP, XLOOKUP, and MATCH all silently return only the first row that matches a SKU — a duplicated code's second row is simply never seen by the lookup. FILTER can surface every row for a code you're checking:

   =FILTER(Current!A2:B, Current!A2:A="A-820")

   If that returns more than one row, the SKU is duplicated in that file — review it by hand rather than trusting whichever row a lookup happened to pick.

Getting a Google Sheet into a file comparison tool

A Google Sheet isn't itself a CSV or XLSX file — if you want to hand the data to a tool that reads files rather than live Sheets links, export it first: File > Download, then Comma Separated Values (.csv) or Microsoft Excel (.xlsx). Each sheet in a multi-sheet spreadsheet exports separately, so export Previous and Current as two files.

Where a spreadsheet gets harder to maintain

FILTER and QUERY remove some of the manual dragging-down and re-sorting Excel formulas need, but the underlying problem is the same: it gets heavier when the supplier changes the column layout between files, when duplicated identifiers need judgement one by one, when the comparison runs every quarter rather than once, or when you also need the margin impact alongside the cost movement. When the spreadsheet becomes cumbersome, compare the files in CostRift instead.

Compare the files in one step instead

CostRift runs this same comparison without the formulas. Export your two sheets as CSV or XLSX, then map the SKU and cost columns in each — independently, so the column names don't need to match between files. It matches items by identifier, classifies every matched row as increased, decreased, or unchanged with the absolute and percentage change, turns unmatched rows into new or removed items, and holds back rows it can't pair cleanly — including duplicated identifiers — for review rather than guessing. Files are read and compared in your browser and are not uploaded to run the comparison.

A worked example

Six items from one supplier, previous cost against current cost. Numbers are illustrative and in any currency.

A-540 shows up only in FILTER's new-items result; A-110 shows up only in the removed-items version run the other way. A-820 appears twice in the current list — confirmed by filtering for that code directly — so it is reviewed by hand rather than paired with whichever row a lookup happened to return first.

Frequently asked questions

Where to go next

For the same comparison across a whole price list without the formulas, see CostRift's supplier price list comparison. Working in Excel instead of Sheets? The lookups differ slightly — see comparing two supplier price lists in Excel. When the point of the comparison is a cost increase, responding to a supplier price increase and the margin erosion calculator go into the margin maths that follows.