How to compare two price lists and find changed prices in Excel
Excel formula
=LET(old,XLOOKUP(D2:D8,A2:A7,B2:B7,"New"),FILTER(HSTACK(D2:D8,old,E2:E8),old<>E2:E8))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)).
- Result on the example
- a 4-row list that spills from G2 in
G2 - Works in
- Excel 2024 and Microsoft 365
- Google Sheets
- Different formula, see below
See it work
The formula is already in G2. Change any input and the result updates, or edit the formula to experiment.
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.
=LET(old,XLOOKUP(D2:D8,A2:A7,B2:B7,"New"),FILTER(HSTACK(D2:D8,old,E2:E8),old<>E2:E8))Result{4 rows × 3 cols} "Espresso beans 1 kg", 27.9, 29.5, "Paper cups (100)", …
old{7 rows × 1 col} 18.2, 27.9, 9.25, 9.5, …A name defined in LET, or a named range.
D2:D87 rows × 1 col: "Oat milk 6-pack", "Espresso beans 1 kg", "Green tea 100 bags", "Paper cups (100)", …A range: 7 cells in one column.
A2:A76 rows × 1 col: "Espresso beans 1 kg", "Oat milk 6-pack", "Paper cups (100)", "Green tea 100 bags", …A range: 6 cells in one column.
B2:B76 rows × 1 col: $27.90, $18.20, $9.50, $9.25, …A range: 6 cells in one column.
XLOOKUP(D2:D8,A2:A7,B2:B7,"New"){7 rows × 1 col} 18.2, 27.9, 9.25, 9.5, …XLOOKUP: Searches one range and returns the matching item from another.- lookup_value
D2:D87 rows × 1 col: "Oat milk 6-pack", "Espresso beans 1 kg", "Green tea 100 bags", "Paper cups (100)", … - lookup_array
A2:A76 rows × 1 col: "Espresso beans 1 kg", "Oat milk 6-pack", "Paper cups (100)", "Green tea 100 bags", … - return_array
B2:B76 rows × 1 col: $27.90, $18.20, $9.50, $9.25, … - if_not_found
"New"
- lookup_value
E2:E87 rows × 1 col: $18.20, $29.50, $9.25, $9.95, …A range: 7 cells in one column.
HSTACK(D2:D8,old,E2:E8){7 rows × 3 cols} "Oat milk 6-pack", 18.2, 18.2, "Espresso beans 1 kg", …HSTACK: Places ranges side by side.- array1
D2:D87 rows × 1 col: "Oat milk 6-pack", "Espresso beans 1 kg", "Green tea 100 bags", "Paper cups (100)", … - array2
old{7 rows × 1 col} 18.2, 27.9, 9.25, 9.5, … - array2
E2:E87 rows × 1 col: $18.20, $29.50, $9.25, $9.95, …
- array1
old<>E2:E8{7 rows × 1 col} FALSE, TRUE, FALSE, TRUE, …The <> operator tests whether they differ.
FILTER(HSTACK(D2:D8,old,E2:E8),old<>E2:E8){4 rows × 3 cols} "Espresso beans 1 kg", 27.9, 29.5, "Paper cups (100)", …FILTER: Rows of a range that meet every condition. #N/A when none match.- range
HSTACK(D2:D8,old,E2:E8){7 rows × 3 cols} "Oat milk 6-pack", 18.2, 18.2, "Espresso beans 1 kg", … - condition1
old<>E2:E8{7 rows × 1 col} FALSE, TRUE, FALSE, TRUE, …
- range
LET(old,XLOOKUP(D2:D8,A2:A7,B2:B7,"New"),FILTER(HSTACK(D2:D8,old,E2:E8),old<>E2:E8)){4 rows × 3 cols} "Espresso beans 1 kg", 27.9, 29.5, "Paper cups (100)", …LET: Names values inside a formula.- name1
old{7 rows × 1 col} 18.2, 27.9, 9.25, 9.5, … - value1
XLOOKUP(D2:D8,A2:A7,B2:B7,"New"){7 rows × 1 col} 18.2, 27.9, 9.25, 9.5, … - name2, value2
FILTER(HSTACK(D2:D8,old,E2:E8),old<>E2:E8){4 rows × 3 cols} "Espresso beans 1 kg", 27.9, 29.5, "Paper cups (100)", …
- name1
In Google Sheets
Google Sheets needs a different formula: =LET(old,ARRAYFORMULA(IFNA(VLOOKUP(D2:D8,A2:B7,2,FALSE),"New")),FILTER(HSTACK(D2:D8,old,E2:E8),old<>E2:E8)). On the same data it gives the same result. Here it is running in Google Sheets mode.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =IF(XLOOKUP(D2,$A$2:$A$7,$B$2:$B$7,"New")<>E2,"Changed","") | Different result | 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.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Compare two price lists and find changed prices
G2, list every item whose price changed (or that is new), with its old and new price.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).
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.