Level 8 · Boss: update the kitchen price list

A short list applied to a long one

Price changes rarely arrive as a full new catalog. Someone sends the products that change, and the analyst applies them to the full list. This boss level does that for the twenty Harbor & Pine kitchen products, using everything from the chapter: a lookup with a fallback, a formula filled down, and an IFS rule with its bands in the right order.

The sheet holds the kitchen list in A1:C21, with each product’s February price (the last price it sold at before March 1). The change list Rachel received is in H1:I7: six product ids and their price from March 1, sorted by name, not by id. The threshold for a big raise is in I9. Columns D, E and F are yours.

Step 1: new prices with a fallback

D2   =XLOOKUP(A2,$H$2:$H$7,$I$2:$I$7,C2)

Product 142 is in the change list, so D3 returns its March price, 38.99. Product 141 isn’t, so D2 falls back to C2, its own February price of 13.99. The fallback is a cell, not text: “not in the change list” means “price unchanged”, and the unchanged price is right there on the row. The change list is locked with $ because every row searches the same six rows; C2 stays relative so each row falls back to its own price.

Step 2: the change in percent

E2   =D2/C2-1

Dividing the new price by the old one gives 1.0541 for product 142; subtracting 1 leaves 0.0541, which the percentage format shows as 5.4%. Unchanged products give exactly 0. Don’t multiply by 100: the format already does that for display, and the threshold in I9 is stored as 0.06.

Step 3: labels with IFS

F2   =IFS(E2<0,"cut",E2=0,"same",E2>=$I$9,"big raise",TRUE,"raise")

As with the shipping bands in level 1, the order of the pairs is the rule. “big raise” must come before the catch-all TRUE, or every raise would stop at “raise”. Fourteen products come out “same”, four “raise” and two “big raise”: the Pine Cast Iron Carafe (143) at 7.4% and the Thatcher Cast Iron Bowl Set (146) at 6.3%. Those two need new shelf cards.

Thinking like an analyst

There are no cuts in this price list, yet the formula tests for them. A rule is written for every list that will pass through the sheet, not just this one. Run two sanity checks before you send it:

• The labels add up: 14 + 4 + 2 is 20, one per product.
• The number of changed prices matches the change list: six rows in H2:H7, six products not labelled “same”. If those two counts disagreed, an id in the change list would be missing from the kitchen list, or typed as text.

Common mistakes

• No fallback in step 1: fourteen #N/A cells, which spread into columns E and F.
• A text fallback such as "unchanged": step 2 then divides text and shows #VALUE!.
• =(D2-C2)/C2*100 on a percent-formatted column: 5.4% shows as 540.7%.
• Testing E2>0 for “raise” before E2>=$I$9: the big raises never get their label.