Convert at the rate in force on the invoice date (approximate VLOOKUP)
The problem
Problem statement
Amounts are in EUR in C2:C2 and the dated rate table is in F2:G4, sorted oldest first. Convert row 2 to GBP at the rate in force on its invoice date, rounded to two decimals, in the GBP cell D2.
The rate is the one in force on the invoice date, not today's rate and not the latest one.
Problem data
Show the sheet as a table (selectable, copyable)
| A | B | C | D | F | G | |
|---|---|---|---|---|---|---|
| 1 | Invoice | Date | Amount (EUR) | GBP | Rate date | GBP per EUR |
| 2 | INV-901 | 2026-02-10 | 1000 | 2026-01-01 | 0.85 | |
| 3 | INV-902 | 2026-03-15 | 500 | 2026-02-01 | 0.87 | |
| 4 | 2026-03-01 | 0.9 |
Notes
A dated rate table is an approximate match: you want the last rate whose date is not after the invoice date, so VLOOKUP's fourth argument is TRUE and the table must stay sorted. Using FALSE finds nothing unless the invoice landed exactly on a rate date.
How to approach
Your answer goes in D2.
- Target cell is D2
- Look the invoice date up in F2:G4
- Approximate match, table sorted ascending
- Shape: =ROUND(?*VLOOKUP(?, ?, ?, ?), ?)
Function guide
FAQ
How do I convert at a dated exchange rate?
=amount * VLOOKUP(invoice_date, rate_table, 2, TRUE) with the table sorted by date. Approximate match picks the last rate not after the date.
Why does my FX conversion return #N/A?
Either the table is not sorted, the date is before the first rate, or the fourth argument is FALSE and no date matches exactly.