To look up an exchange rate by date when some dates have no rate (weekends, holidays), use XLOOKUP with match mode -1: =XLOOKUP(A2, Rates!$A$2:$A$400, Rates!$B$2:$B$400, "No rate", -1). It returns the rate for the exact date if one exists, otherwise the rate for the nearest earlier date.
How does the formula work?
- A2 is the transaction date.
- Rates!$A$2:$A$400 is the list of rate dates and Rates!$B$2:$B$400 the rates.
- "No rate" is what appears if the transaction date is earlier than every date in the table.
- -1 is the match mode: exact match, or the next smaller item if there is none.
XLOOKUP is available in Excel 365 and Excel 2021. In older versions use =INDEX(Rates!$B$2:$B$400, MATCH(A2, Rates!$A$2:$A$400, 1)), which needs the rate dates sorted from oldest to newest.
Can you show a worked example?
Suppose the Rates sheet has these hypothetical USD to INR rates for early January 2025. There is no row for the weekend.
| Rate date | Day | INR per USD |
|---|---|---|
| 01-Jan-2025 | Wednesday | 85.60 |
| 02-Jan-2025 | Thursday | 85.70 |
| 03-Jan-2025 | Friday | 85.50 |
| 06-Jan-2025 | Monday | 85.90 |
| Transaction date | USD spent | Rate found | INR |
|---|---|---|---|
| 03-Jan-2025 | 100 | 85.50 (exact) | 8,550 |
| 04-Jan-2025 | 20 | 85.50 (Friday's rate) | 1,710 |
| 05-Jan-2025 | 10 | 85.50 (Friday's rate) | 855 |
| 06-Jan-2025 | 50 | 85.90 (exact) | 4,295 |
The Saturday and Sunday rows borrow Friday's rate, which is the usual convention when no rate is published. For example, 20 x 85.50 = ₹1,710.
Why do I still get wrong or empty results?
- Dates stored as text. A date that is left-aligned or shows a green corner is probably text. Convert both the table and the transactions to real dates.
- Times inside dates. A transaction on 4 Jan at 14:30 is still after the 4 Jan rate date, so it works, but a rate date that carries a time may land after the transaction. Strip times with =INT(A2).
- Dates before the first rate. XLOOKUP returns your message ("No rate"). Add earlier rates or accept an estimate.
- A range that is too short. Extend the ranges past the last row so new rates are included.
Can one table hold several currencies?
Yes. Put dates down column A and one currency per column, with codes in the header row: B1 USD, C1 EUR, D1 GBP. Then pick the column using the code in D2 of the transaction sheet with a nested INDEX: =XLOOKUP(A2, Rates!$A$2:$A$400, INDEX(Rates!$B$2:$D$400, 0, MATCH(D2, Rates!$B$1:$D$1, 0)), "No rate", -1). MATCH finds which column the currency sits in, INDEX with a row number of 0 hands back that whole column, and XLOOKUP does the date search inside it. Add an INR row to the transactions with a plain IF(D2="INR",1,...) around the formula, since INR has no column of its own.
Does the table have to be sorted?
For the plain XLOOKUP above, no. The default search mode works through the list in order and handles unsorted dates. The MATCH version for older Excel does need ascending order, and so does any binary search mode (2 or -2). If you are unsure, sort the rate table by date, oldest first; it costs nothing.
Checks before you trust the column
- =COUNTIF($E$2:$E$500,"No rate") should be 0.
- Spot-check a Saturday and a Monday against the table.
- Make sure the rate table has one row per date, with no duplicates.
- Add a column that shows the date of the rate used, with a second XLOOKUP that returns the date column, so you can see which day each row borrowed from.
- Name the two ranges (Formulas, Define Name) as RateDates and RateValues; the formula then reads =XLOOKUP(A2, RateDates, RateValues, "No rate", -1), which is easier to review.
Where do rates come from? Use whichever source you have chosen to rely on, and note it above the table. If your card billed a different rupee amount, the statement figure is what you actually paid.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


