# How to look up a rate for a date or the closest date before it

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.

**Excel:** `=XLOOKUP(E1,A2:A9,B2:B9,,-1)` (Excel 2021, 2024 and Microsoft 365)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E |
| --- | --- | --- | --- | --- |
| Date | EUR to USD |  | Invoice date | 2026-03-14 |
| 2026-03-09 | 1.0842 |  | Rate |  |
| 2026-03-10 | 1.0867 |  |  |  |
| 2026-03-11 | 1.0851 |  |  |  |
| 2026-03-12 | 1.0889 |  |  |  |
| 2026-03-13 | 1.0874 |  |  |  |
| 2026-03-16 | 1.0903 |  |  |  |
| 2026-03-17 | 1.0915 |  |  |  |
| 2026-03-18 | 1.0898 |  |  |  |

Formula in E2: `=XLOOKUP(E1,A2:A9,B2:B9,,-1)`

Result: 1.0874

## 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.

## Other ways

- `=VLOOKUP(E1,A2:B9,2,TRUE)`: The classic approximate match, for every version. The dates must be in ascending order.
- `=INDEX(B2:B9,MATCH(E1,A2:A9,1))`: 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.

## 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)`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/rate-for-date-or-previous