Level 6 · Boss: compare February's price list with today's catalog

Two lists that should match, and don’t

Thomas has two versions of the same thing: the Decor products the catalog sells today, and the Decor price list from February 2025, rebuilt from what customers actually paid that month. Lists like these never match row for row. Products launch, products are discontinued, prices move. Reconciling them is the most common “consolidation” job of all, and it uses everything in this chapter: matching by key instead of position, and a master table built with lookups.

The sheet holds today’s catalog in A3:C19 (17 active products, sorted by id) and February’s list in A23:C38 (16 products, sorted by name). Different lengths and different orders: the only safe way to compare them is by product_id.

Step 1: what’s missing on each side

COUNTIF(range, value) counts how often a value occurs. Used on the other list, 0 means “not there”:

D3    =IF(COUNTIF($A$23:$A$38,A3)=0,"no","yes")
D23   =IF(COUNTIF($A$3:$A$19,A23)=0,"no","yes")

Fill each down its own block. The catalog side finds three “no”: products 182, 195 and 197, all launched after February. The February side finds two: 183 (Glen Glass Canvas Print) and 191 (Hazel Ceramic Canvas Print), both discontinued since. Both directions matter: a one-way check would never see the dropped products.

Step 2: bring February’s price over

Now the catalog becomes the master table. For each product, fetch February’s price:

E3    =XLOOKUP(A3,$A$23:$A$38,$C$23:$C$38,"")

The February list is sorted by name, so position is useless, but XLOOKUP doesn’t care. The fourth argument handles the three new products: an empty text instead of #N/A. With INDEX/MATCH you would write =IFERROR(INDEX($C$23:$C$38,MATCH(A3,$A$23:$A$38,0)),""). =IFERROR(VLOOKUP(A3,$A$23:$C$38,3,FALSE),"") works too, because the id is the block’s first column; forget the FALSE and the unsorted list returns wrong prices.

Step 3: the change

F3    =IF(E3="","",C3/E3-1)

The IF skips products with nothing to compare. For the rest, today’s price over February’s, minus 1, formatted as a percentage. Product 194, the Chester Brass Wall Mirror, went from 149.00 to 159.00: 6.7%. Product 185, the Ridge Brass Bud Vase, went from 12.00 to 12.99: 8.3%, the biggest rise in percentage terms.

Step 4: the summary

I2    =COUNTIF(D3:D19,"no")       3 new since February
I3    =COUNTIF(D23:D38,"no")      2 dropped since February
I4    =COUNTIF(F3:F19,">0")       5 prices raised

A sanity check that ties it together: the 17 catalog products minus the 3 new ones leaves 14 matched products; February’s 16 minus the 2 dropped leaves the same 14. If those two numbers ever differ, an id is duplicated or typed differently in one list.

Thinking like an analyst

The two lists differ for three separate reasons, and Thomas needs them apart: new products (no old price to compare), discontinued products (nothing to price), and real price changes. Mixing them, for example by comparing totals of the two price columns, would give one meaningless number. Count each kind on its own.

Also note where February’s prices came from: the order lines, not a catalog snapshot. A product that was in the catalog in February but sold nothing that month would show up as “new”. When a reconciliation looks surprising, check how each list was produced before you trust the gap.

Common mistakes

• Comparing in one direction only, and missing the dropped products.
• Unlocked lookup ranges: A23:A38 filled down becomes A24:A39, and the last rows stop finding products at the top of the list.
• "<>0" in step 4: it counts the empty texts of new products as price changes.
• Comparing by position: row 3 of each list holds a different product.