Match two price lists on SKU when the columns are in a different order
Last quarter the SKU was column A and the price column C. This quarter the price comes first, the SKU column is called "Item #", and there is a List Price next to the Net Price. VLOOKUP with a fixed column number breaks on exactly this. These lookups do not.
1. XLOOKUP ignores column order
XLOOKUP takes the column to search and the column to return as two separate ranges, so they can be anywhere. With the old list's SKU in Old!A2 and the new list's SKU in column B and Net Price in column A:
=XLOOKUP(A2,New!$B:$B,New!$A:$A,"Not in new list")That still breaks if the columns move again next quarter. The next method does not.
2. Find the columns by their header name
MATCH on the header row returns a column's position, whatever it is this time:
=MATCH("Item #",New!$1:$1,0)Put that inside INDEX twice, once to find the SKU column and once to find the price column. In Old!D2, fill down:
=IFERROR(INDEX(New!$A:$Z,MATCH($A2,INDEX(New!$A:$Z,0,MATCH("Item #",New!$1:$1,0)),0),MATCH("Net Price",New!$1:$1,0)),"Not in new list")When the supplier renames a header, you change one word in the formula, not every range. Match on the exact price you pay: if the list has both List Price and Net Price, pick the one you were comparing last time, every time.
3. Power Query, for lists you compare every month
- Select each list and use Data, From Table/Range to load both into Power Query.
- Home, Merge Queries. Pick the old table, then the new one, and click the SKU column in each (they may have different names).
- Join kind Left Outer keeps every old line with its new price. Left Anti returns only the dropped SKUs; swap the tables and use it again for new SKUs.
- Expand the new price column, add a custom column for the change, Close and Load. Next month, paste the new list over the old data and click Refresh.
Worked example
Old header: SKU, Description, Price. New header: Net Price, Item #, Name, UOM, List Price. The two MATCH calls return 2 for "Item #" and 1 for "Net Price", so the INDEX formula reads the price from column 1 at the row where column 2 holds the SKU:
| SKU | Description | Old price | New net price | Change |
|---|---|---|---|---|
| FS-100 | Conduit strap 3/4in (25) | $12.00 | $12.60 | +5.0% |
| FS-120 | Cable tie 8in (100) | $3.20 | $3.42 | +6.9% |
| FS-130 | Wire nut assortment | $45.00 | $47.25 | +5.0% |
Guessing columns from header names is also how the free price list compare tool works: on this header it picks "Item #" as the SKU and "Net Price" as the price, and lets you change either before comparing.
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.