How to compare supplier price lists by SKU

1. Check the price basis by hand

A price comparison is only meaningful if both files describe the same thing. Before matching anything, open both files and confirm with the supplier's covering note or terms:

• Unit and pack size: per item, per box, per roll or per case.
• Currency, and whether it is the same in every row.
• Whether prices include or exclude VAT, and any trade discount already applied.

The sample files have a Currency column but do not state VAT treatment or pack basis explicitly, so you cannot infer those from the data. Neither the formulas below nor Catalogix detect or convert units, packs or VAT for you. If the basis has changed, stop and resolve that first.

2. Import both files into Excel

These instructions are for modern Excel (Microsoft 365, Excel 2021 or Excel 2024). Opening a CSV by double-clicking lets Excel convert values automatically, which can strip leading zeros or alter codes, so import instead:

1. In a new workbook choose Data > From Text/CSV and select the previous file.
2. Choose Transform Data. Set the Supplier SKU column type to Text before any automatic type change is applied (remove an automatic "Changed Type" step if needed), so leading zeros and spaces are preserved exactly.
3. For Cost Price, use Change Type > Using Locale, choose Decimal Number with English (United Kingdom), so the decimal point is read correctly.
4. Load to a sheet and rename it Previous. Repeat for the new file on a sheet named Current.

Keep the original columns in A:E: Supplier SKU, Product Name, Category, Cost Price, Currency. Headers are on row 1. With the sample files, data runs from Previous!A2:A51 (50 rows) and Current!A2:A52 (51 rows). If Excel loads a table, you can convert it to a range so the plain A1 formulas below work as written.

3. Check identifiers and prices on both sheets

Add headers in row 1: F SKU check and G Checked price on both sheets. Enter the formula in row 2, then copy down to row 51 on Previous and row 52 on Current. The SKU check uses EXACT, which is case-sensitive, so it flags blanks, non-text values and duplicates.

=IF(OR(NOT(ISTEXT(A2)),A2=""),"Check SKU",IF(SUMPRODUCT(--EXACT(A2,$A$2:$A$51))>1,"Duplicate SKU","OK"))

=IF(OR(NOT(ISTEXT(A2)),A2=""),"Check SKU",IF(SUMPRODUCT(--EXACT(A2,$A$2:$A$52))>1,"Duplicate SKU","OK"))

=IF(ISNUMBER(D2),IF(D2>=0,D2,"Check price"),"Check price")

Resolve every "Duplicate SKU" and "Check SKU" before comparing. The lookup in the next step returns the first match only, so a duplicate would silently hide the second row. The checked price returns "Check price" for blank, text, invalid or negative input. A price of 0 is allowed, but a percentage from an old price of 0 is not defined. Do not replace blanks with 0 or wrap formulas in IFERROR(...,0): that turns missing data into a fake price.

4. Compare prices on the Current sheet

On Current, add headers H Old price, I Status, J Price change and K Percentage change. Enter in row 2 and copy down to row 52. The old price uses XLOOKUP with EXACT and match mode 0, so matching stays case-sensitive.

=XLOOKUP(TRUE(),EXACT(A2,Previous!$A$2:$A$51),Previous!$G$2:$G$51,"Not in previous",0)

=IF(NOT(ISNUMBER(G2)),"Check price",IF(H2="Not in previous","New in this file",IF(NOT(ISNUMBER(H2)),"Check price",IF(G2>H2,"Price rise",IF(G2<H2,"Price fall","Unchanged price")))))

=IF(AND(ISNUMBER(G2),ISNUMBER(H2)),G2-H2,"")

=IF(AND(ISNUMBER(G2),ISNUMBER(H2)),IF(H2=0,"Not defined",(G2-H2)/H2),"")

Format column K as Percentage with two decimal places. The formula already returns a fraction, so do not multiply by 100.

5. Find rows missing from the new file

On Previous, add header H Reverse check, enter in row 2 and copy down to row 51.

=IF(SUMPRODUCT(--EXACT(A2,Current!$A$2:$A$52))=0,"Missing from new file","Present")

"New in this file" and "Missing from new file" describe file membership only. A missing row does not mean the product is discontinued, and must not be deleted automatically. A "new" and a "missing" row may be the same product under a changed or reassigned SKU: check those pairs against the supplier's code crosswalk or ask the supplier.

6. Optional: warning keys for near-duplicate SKUs

Supplier codes sometimes differ only by case, stray spaces or non-breaking spaces (CHAR(160)). A normalised review key surfaces these. Add L Review key and M Similar-key count on each sheet.

=UPPER(TRIM(SUBSTITUTE(A2,CHAR(160)," ")))

=SUMPRODUCT(--($L$2:$L$51=L2))

=SUMPRODUCT(--($L$2:$L$52=L2))

Worked example with the sample files

The sample files are fictitious office supplies from one supplier. Following the steps above gives these results:

Previous rows

50

Current rows

51

Price rises

5

Price falls

1

New in this file

2

Missing from new file

1

Unchanged price

43

Prices are in EUR, as stated in both sample files; VAT and pack basis are not specified. "Not applicable" means the row exists in only one file.

Older Excel versions

XLOOKUP is not available in Excel 2016 or 2019. There you can use VLOOKUP in Current!H2 instead. It returns column 7 (the checked price in G) with exact match (FALSE()):

=IFNA(VLOOKUP(A2,Previous!$A$2:$G$51,7,FALSE()),"Not in previous")

When Excel is enough, and when to use Catalogix

For a small, clean, occasional update, the workbook above is enough. When the same suppliers send new versions repeatedly, rebuilding columns and ranges each time becomes the work. Catalogix keeps a saved field mapping per supplier and a review step between versions:

1. Add the supplier.
2. Import the previous CSV or XLSX, map the fields and select Supplier SKU as the product key.
3. Import the new version from the same supplier.
4. Review rises, falls, new and missing rows.
5. Export a CSV for your own import process.

It compares versions from one supplier at a time; it does not read PDFs, match across suppliers or sync to an ERP. See the sample workflow or compare plans.