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. Keep an untouched copy of each original. Formatting a number as Text after Excel has removed leading zeros cannot recover them; reimport from the source when needed.

The formulas below use English function names and comma argument separators in Excel with XLOOKUP, such as Microsoft 365 or Excel 2021/2024. Localized Excel may use different names or separators. XLOOKUP is unavailable in Excel 2016/2019. See Microsoft’s XLOOKUP reference.

2. Resolve duplicate and blank SKUs before looking up prices

On each sheet, put this temporary check in an empty column and fill down through the product rows. It counts case-sensitive matches within 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 means the identifier occurs once. A larger count means there are duplicates to resolve with the supplier. Do not let a lookup silently choose one of two prices. Adjust $5001 to include your whole list, and use the same ending row in every formula.

EXACT compares text with case sensitivity: ABC and abc are different identifiers. These Excel formulas also distinguish spaces. CostLatch trims surrounding spaces before matching; normalize those deliberately if you want both methods to agree.

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 into spare columns using old column C for pack and D for currency. Compare those values with the new row. Calculate a price change only when SKU, pack, currency and cost basis are comparable.

4. Calculate changes and check both directions

For rows already cleared by the checks above, use these formulas. In F2, calculate the cost difference:

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

In G2, calculate the proportional change 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 defined percentage increase, so it needs separate review. These two formulas do not independently detect duplicate, unit or currency problems; finish steps 1–3 first.

Also look up the old SKUs in the new sheet to find products missing from the update. “Not in old list” could mean a new item or a renamed SKU. “Missing from new list” does not establish that the supplier discontinued it.

Practice with a complete supplier update

These fictional files contain one increase, one decrease, one unchanged item, one added item, one missing item and two review cases: a changed pack and a duplicate SKU.

Examples are free to adapt. They are not supplier quotes or files to import into a live store.

For a selling-price review, gross margin differs from markup: an $11 cost at 30% gross margin requires $11 ÷ 0.70, rounded up to $15.72. Include relevant costs first; that calculation is not a profit guarantee.

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

When this method is a fit

Use it for stable identifiers from one supplier. A PDF scan, different suppliers’ SKU systems, changed product definitions or unrecorded pack quantities need additional preparation. CostLatch supports CSV comparison and a review export; it does not read PDFs, match product names or update your store automatically.

Review the result before changing your catalog. If a step is unclear, tell us about the workflow without attaching confidential price files.