📎 This article includes 1 downloadable practice file ↓
Prices, exchange rates and tax rates change over time. An order placed on 10 July must use the price that was valid on 10 July — not today’s price, and not the July 15 increase. That’s an “effective-date lookup”: find the latest rate whose start date is on or before the transaction date.
In this article
The price history
| Product | Valid from | Price |
|---|---|---|
| Monitor | 01-Apr-2026 | 11,499 |
| Monitor | 15-Jul-2026 | 11,999 |
| Monitor | 01-Sep-2026 | 11,299 |
One product: XLOOKUP, next smaller
=XLOOKUP(A2, History[Valid from], History[Price], "No price", -1)Match mode -1 means “exact match, or the next smaller value”. For an order on 10-Aug it finds 15-Jul and returns 11,999. The history doesn’t even need to be sorted.
Many products: add the product condition
=XLOOKUP(1, (History[Product]=E2) * (History[Valid from]=MAXIFS(History[Valid from], History[Product], E2, History[Valid from], "<="&A2)), History[Price], "No price")MAXIFS finds the latest start date for this product on or before the order date; XLOOKUP returns the price on that row.
Older Excel: LOOKUP on sorted data
=LOOKUP(2, 1/((History!A2:A100=E2)*(History!B2:B100<=A2)), History!C2:C100)This returns the last matching row, so sort the history by product and date ascending first.
Real uses
- Exchange rates: a daily rate table, lookup with -1 so weekends use Friday’s rate.
- Salary revisions: the salary valid on each month’s payroll date.
- Tax rate changes: the rate effective on the invoice date (verify against official notifications — the table here is just the mechanism).
Where people go wrong
| Problem | Cause |
|---|---|
| Returns the newest price for old orders | Used exact match on product only — ignored dates |
| #N/A for early orders | Order is before the first “valid from” date — that’s correct; add a starting row |
| Wrong price with LOOKUP | History not sorted ascending |
| Dates don’t compare | One side is text — check with ISNUMBER |
Practice
Download the combo practice workbook below: a Sales sheet of 200 orders plus tasks for the formulas on this page, each with an automatic ✓ check and an Answers sheet.
More: all formula combos · Excel function course.
📎 Practice files for this article
- 📗Formula combinations practice workbook50 tasks: lookups, running totals, ranks, top-N, unique counts, FY, age, text extraction, list comparison, weighted averages, ageing, rolling totals, duplicates and working days — with automatic checks.⬇ XLSX · 39 KB
Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.
Stuck on a step? Ask a question and the AI answers using this article.