What did that pizza actually cost?
You run a pizza shop and your prices change over time. The transactions table logs every order with the date it was placed. The price_history table logs every price change, so each pizza appears several times, one row per change, sorted oldest first. Order 103 was placed on 2026-02-10 for pizza #4. You can't use today's menu price, and you can't do a plain lookup because pizza #4 has four different prices. You need the price that was in effect on the order date.
What did that pizza actually cost?
Practice using FILTER + VLOOKUP to solve a real general problem. By the end, you’ll know when to reach for it and how to structure the arguments correctly.
You run a pizza shop and your prices change over time. The transactions table logs every order with the date it was placed. The price_history table logs every price change, so each pizza appears several times, one row per change, sorted oldest first. Order 103 was placed on 2026-02-10 for pizza #4. You can't use today's menu price, and you can't do a plain lookup because pizza #4 has four different prices. You need the price that was in effect on the order date.
In cell J4 (the 'Charged price' column, on order 103's row), return the price pizza #4 was actually charged on 2026-02-10. Narrow price_history down to just pizza #4's price changes with FILTER, then look the order date up in that filtered schedule with VLOOKUP. Leave VLOOKUP's last argument off so it uses approximate match and lands on the most recent price on or before the order date.
FILTER returns the rows of a range that match a condition, spilling the matching rows into the cells below. The modern dynamic-array way to subset a list without copy-paste, autofilter, or pivot tables. Available in Microsoft 365 and Excel 2021+.
FILTER(array, include, [if_empty])Try the formula first. Hints cost CP for a reason.