Lookup & Reference
Effective-Date Rate & Price Lookup by Transaction Date (XLOOKUP Mode -1 & VLOOKUP)
Resolve the historical price, billing rate, or FX rate in effect on any transaction date using XLOOKUP with match_mode = -1 (exact or next earlier effective date) while guarding dates prior to contract inception.
Match the latest effective date on or before the transaction date
Pricing schedules, union wage scales, and SaaS seat tiers take effect on discrete revision dates (for example, Jan 1, Apr 1, and Sep 1). Exact-match lookups fail for every transaction dated in between revisions, whereas VLOOKUP(..., TRUE) silently requires the effective-date table to be sorted ascending.
Using XLOOKUP(txDate, effDates, rates, "Prior to Effective Schedule", -1) sets match_mode = -1 (exact match or next smaller date), which resolves the exact rate active on txDate regardless of sort order and cleanly catches pre-inception dates instead of returning #N/A.
| Effective Date | Hourly Rate | Sample Invoice Date | Resolved Rate |
|---|---|---|---|
| 2026-01-01 | $125 | 2025-12-15 | "Prior to Effective Schedule" |
| 2026-04-01 | $140 | 2026-04-01 | $140 (Exact revision boundary) |
| 2026-07-15 | $155 | 2026-06-20 | $140 (Apr 1 tier still active) |
| 2026-10-01 | $165 | 2026-11-10 | $165 (Latest tier active) |
Effective-Dated Rate Lookup (XLOOKUP Exact or Next Earlier Date)
Finds the billing rate in B2:B5 whose effective date in A2:A5 is on or immediately before transaction date D2.
=XLOOKUP(D2, $A$2:$A$5, $B$2:$B$5, "Prior to Effective Schedule", -1)Extended Line Amount With Pre-Inception Guard
Multiplies billable hours E2 by the effective rate active on D2, returning 0 if the date precedes the earliest schedule row.
=LET(rate, XLOOKUP(D2, $A$2:$A$5, $B$2:$B$5, -1, -1), IF(rate < 0, "Invalid Date", ROUND(E2 * rate, 2)))Try the example
Use the original values or change them to test your understanding.