# How to compare two price lists and find changed prices in Excel

Look up each item's old price and keep the rows where it differs: `=LET(old,XLOOKUP(D2:D8,A2:A7,B2:B7,"New"),FILTER(HSTACK(D2:D8,old,E2:E8),old<>E2:E8))`.

**Excel:** `=LET(old,XLOOKUP(D2:D8,A2:A7,B2:B7,"New"),FILTER(HSTACK(D2:D8,old,E2:E8),old<>E2:E8))` (Excel 2024 and Microsoft 365)

**Google Sheets:** `=LET(old,ARRAYFORMULA(IFNA(VLOOKUP(D2:D8,A2:B7,2,FALSE),"New")),FILTER(HSTACK(D2:D8,old,E2:E8),old<>E2:E8))`

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F | G |
| --- | --- | --- | --- | --- | --- | --- |
| Item | Old price |  | Item | New price |  | Changed prices |
| Espresso beans 1 kg | 27.9 |  | Oat milk 6-pack | 18.2 |  |  |
| Oat milk 6-pack | 18.2 |  | Espresso beans 1 kg | 29.5 |  |  |
| Paper cups (100) | 9.5 |  | Green tea 100 bags | 9.25 |  |  |
| Green tea 100 bags | 9.25 |  | Paper cups (100) | 9.95 |  |  |
| Cocoa powder 500 g | 12.4 |  | Vanilla syrup | 8.25 |  |  |
| Vanilla syrup | 7.95 |  | Cocoa powder 500 g | 12.4 |  |  |
|  |  |  | Caramel syrup | 8.25 |  |  |

Formula in G2: `=LET(old,XLOOKUP(D2:D8,A2:A7,B2:B7,"New"),FILTER(HSTACK(D2:D8,old,E2:E8),old<>E2:E8))`

Result (spills 4 × 3): Espresso beans 1 kg, $27.90, $29.50; Paper cups (100), $9.50, $9.95; Vanilla syrup, $7.95, $8.25; Caramel syrup, New, $8.25

## How it works

XLOOKUP looks up every item of the new list in the old one at once, and LET names the result `old`. Items that aren't in the old list get "New". FILTER keeps the rows where the old price differs from the new one, and HSTACK lays out item, old price and new price side by side.

## Other ways

- `=IF(XLOOKUP(D2,$A$2:$A$7,$B$2:$B$7,"New")<>E2,"Changed","")`: A flag next to each row of the new list, filled down. Works in Excel 2021 and Google Sheets. (A single flag, "", for the first item.)

## Common mistakes

- Prices that look equal can differ by a fraction of a cent. Compare rounded values: `ROUND(old,2)<>ROUND(E2:E8,2)`.
- Items dropped from the new list don't appear. Run the comparison the other way to find them.
- Item names must match exactly in both lists; a code column is safer than names.

## Questions

**How do I show the change in percent?**

Add a column to HSTACK: `E2:E8/old-1`, after filtering out the new items.

**How do I find items that were removed?**

Filter the old list for names missing from the new one: `=FILTER(A2:A7,COUNTIF(D2:D8,A2:A7)=0)`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/compare-two-price-lists