How to look up a rate for a date or the closest date before it
Formula (Excel and Google Sheets)
=XLOOKUP(E1,A2:A9,B2:B9,,-1)Use an approximate match on the dates: =XLOOKUP(E1,A2:A9,B2:B9,,-1) returns the rate on that date or, if there is none, the last one before it.
- Result on the example
- 1.0874 in
E2 - Works in
- Excel 2021, 2024 and Microsoft 365
- Google Sheets
- Same formula
See it work
The formula is already in E2. Change any input and the result updates, or edit the formula to experiment.
How it works
March 14, 2026 is a Saturday, so it isn't in the list. Match mode -1 returns an exact match or the next smaller value, so XLOOKUP falls back to Friday, March 13, and returns 1.0874. A weekday that is in the list returns its own rate.
=XLOOKUP(E1,A2:A9,B2:B9,,-1)Result1.0874
E13/14/2026The value in E1.
A2:A98 rows × 1 col: 3/9/2026, 3/10/2026, 3/11/2026, 3/12/2026, …A range: 8 cells in one column.
B2:B98 rows × 1 col: 1.0842, 1.0867, 1.0851, 1.0889, …A range: 8 cells in one column.
-1-1Makes the value negative.
XLOOKUP(E1,A2:A9,B2:B9,,-1)1.0874XLOOKUP: Searches one range and returns the matching item from another.- lookup_value
E13/14/2026 - lookup_array
A2:A98 rows × 1 col: 3/9/2026, 3/10/2026, 3/11/2026, 3/12/2026, … - return_array
B2:B98 rows × 1 col: 1.0842, 1.0867, 1.0851, 1.0889, … - if_not_found
(omitted) - match_mode
-1-1
- lookup_value
In Google Sheets
The same formula works in Google Sheets: =XLOOKUP(E1,A2:A9,B2:B9,,-1). We ran it in Google Sheets mode on the same data and it returns 1.0874.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =VLOOKUP(E1,A2:B9,2,TRUE) | 1.0874 | The classic approximate match, for every version. The dates must be in ascending order. |
| =INDEX(B2:B9,MATCH(E1,A2:A9,1)) | 1.0874 | MATCH with 1 does the same, also on ascending dates. |
Common mistakes
- A date before the first rate returns #N/A. Make sure the rate table starts early enough.
- An exact match (FALSE or 0) returns #N/A for every weekend and holiday.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Look up a rate for a date or the closest date before it
E2, return the rate for the invoice date in E1, or the most recent rate before it.Questions
How do I use the next available rate instead?
Use match mode 1: =XLOOKUP(E1,A2:A9,B2:B9,,1).
How do I convert the invoice amount?
Multiply by the rate: =amount*XLOOKUP(E1,A2:A9,B2:B9,,-1).
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.