Mission M04 · Excel · 15 min

Bring in product cost with XLOOKUP

Request from your manager

Rafael from the Frozen department wants to know how much gross profit his category made in 2025. Your sales file has prices, but the official unit cost lives in the products list. Look it up, calculate profit per line, and give him the total.

Data

Download the practice file(s):

⬇ sales_flat.xlsx⬇ sales_flat.csv⬇ products.csv

Steps

  1. Open sales_flat.csv (as Table1) and copy products.csv into a new sheet; turn it into a table and name it Products (Table Design > Table Name).
  2. In Table1 add a column unit_cost_lkp with =XLOOKUP([@product_name],Products[product_name],Products[unit_cost],"Not found").
  3. Check that no row says 'Not found' (filter the new column).
  4. Add a column gross_profit with =[@net_sales]-[@quantity]*[@unit_cost_lkp].
  5. Filter category = Frozen and order_date = 2025, or use SUMIFS on gross_profit with those two conditions.
  6. Bonus check: the file's cost column is the line's total cost (quantity × unit cost); net_sales − cost should give the same total.

Check your answer

What was the total 2025 gross profit (net_sales − quantity × unit_cost) for the Frozen category?

Show solution

XLOOKUP finds each product's unit_cost in the Products table. Multiplying by quantity gives line cost; subtracting from net_sales gives gross profit. Frozen made $12,114.51 of gross profit in 2025.

unit_cost_lkp: =XLOOKUP([@product_name],Products[product_name],Products[unit_cost],"Not found")
gross_profit:  =[@net_sales]-[@quantity]*[@unit_cost_lkp]
Total:         =SUMIFS(Table1[gross_profit],Table1[category],"Frozen",Table1[order_date],">="&DATE(2025,1,1),Table1[order_date],"<="&DATE(2025,12,31))

Answer: 12114.51 $

← Your first pivot table: sales by regionOnline share of sales (% of total) →