Compare supplier price lists in Excel by SKU

1. Make the two lists comparable

Put the old data on a sheet named Old and the new data on New. Use one header row and the same four columns on each sheet:

A — SKU

Import as Text so 001 keeps its leading zeros.

B — Cost

Use numeric costs, including a genuine zero where appropriate. Resolve blank or invalid costs first.

C — Pack

Record the quantity or unit, such as each or case of 12.

D — Currency

Use a consistent currency code such as USD.

Check that both costs use the same tax, freight and discount basis, and keep an untouched copy of each original. If Excel has already removed leading zeros, formatting the column as Text won’t bring them back; import the file again instead.

The formulas below need XLOOKUP, which Microsoft 365 and Excel 2021 or later have (Excel 2016 and 2019 don’t). Excel in another language may use different function names or semicolons between arguments. See Microsoft’s XLOOKUP reference.

2. Resolve duplicate and blank SKUs before looking up prices

On each sheet, put this check in an empty column and fill it down. It counts how many times each SKU appears on that sheet:

On a narrow screen, scroll formula boxes sideways to read the complete formula.

=IF(A2="","Review: missing SKU",SUMPRODUCT(--EXACT(A2,$A$2:$A$5001)))

A count of 1 is what you want. Anything higher is a duplicate to sort out with the supplier, because a lookup would silently pick one of the two prices. Change $5001 to the last row of your list, and use the same row in every formula.

EXACT is case-sensitive, so ABC and abc count as different SKUs, as they do in CostLatch.

3. Match the old cost to each new SKU

After resolving duplicates on both sheets, put this in New!E2, label the column Old cost, and fill down:

=XLOOKUP(TRUE,EXACT(A2,Old!$A$2:$A$5001),Old!$B$2:$B$5001,"Not in old list",0)

Repeat the lookup in spare columns for the old pack (column C) and currency (column D). Only calculate a price change when the SKU, pack, currency and cost basis all match.

4. Calculate changes and check both directions

For rows that passed the checks above, put the cost difference in F2:

=IF(ISNUMBER(E2),B2-E2,"Review")

Put the percentage change in G2 and format the column as Percentage:

=IF(AND(ISNUMBER(E2),E2>0),(B2-E2)/E2,"Review")

A cost change from $10 to $11 is +$1 and +10%. An old cost of zero has no percentage change, so check those rows by hand. These formulas don’t catch duplicate, pack or currency problems, so finish steps 1 to 3 first.

Also look up the old SKUs in the new sheet to find products missing from the update. “Not in old list” could be a new item or a renamed SKU, and a SKU missing from the new list isn’t necessarily discontinued.

Practice with a complete supplier update

These fictional lists include a price rise, a price drop, an unchanged item, a new item, a missing item, a changed pack size and a duplicate SKU.

Fictional data, free to reuse. Don’t import it into a real catalog.

If you are setting sell prices, remember that margin is not markup: an $11 cost at a 30% gross margin needs $11 ÷ 0.70, which rounds up to $15.72.

Choose “Load example” in the tool, then “Compare price lists” to see all seven report rows.

When this method is a fit

Both methods need stable SKUs from one supplier. A scanned PDF, two suppliers’ codes or pack sizes nobody recorded need tidying first. CostLatch reads Excel, OpenDocument and CSV files or a copy and paste; it doesn’t read PDFs, match by product name or change your store.

Check the result before changing your catalog. If a step is unclear, tell us, but please don’t attach confidential price files.