How to Look Up an Exchange Rate by Transaction Month in Excel
Question details
The user needs to convert transaction amounts by looking up an exchange rate based on a currency code and the transaction date or month.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Converting financial transaction amounts using a specific foreign exchange (FX) rate corresponding to the month of the transaction.
- Observed behavior
- The goal is to automatically find and apply the correct historical exchange rate from a reference table based on the transaction's date or month.
Ensure your transaction dates and the dates in your FX rate table are formatted as valid Excel dates, not text. For approximate matches using the LOOKUP function, your FX rate table dates must be sorted in ascending order.
Use LOOKUP Function for Sorted Dates
Use this method if your FX rate table is sorted chronologically. It finds the nearest preceding date's exchange rate automatically.
The LOOKUP function is perfect for finding values in a sorted list. When you are looking for an exchange rate based on a date, it will match the exact date or the closest previous date, making it ideal for daily or monthly rate tables.
Click on the cell where you want the converted amount to appear.
Type the formula: `=IF(B2="C",C2/LOOKUP(A2,'FX Rate'!$A$2:$A$8,'FX Rate'!$B$2:$B$8),C2)`. Ensure you replace the cell references with your actual data (e.g., A2 for transaction date, B2 for currency code, C2 for amount).
Press the Enter key to calculate the converted currency amount.
Click and drag the fill handle at the bottom-right corner of the cell down to apply the formula to the rest of your transactions.
Use XLOOKUP with Helper Columns for Exact Month Matches
Use this approach if your dates are unsorted or you need to match the transaction specifically to the exact month rather than the closest date.
Handle Complex Data Lookups Easily in WPS Spreadsheet
WPS Spreadsheet fully supports advanced lookup functions like LOOKUP and XLOOKUP, allowing you to seamlessly manage exchange rates and financial calculations without compatibility issues.
- 1. Open your dataset: Launch WPS Spreadsheet and open your financial workbook.
- 2. Sort the data: Ensure your FX dates are sorted in ascending order using the Sort feature under the Data tab.
- 3. Input formula: Select the target cell and enter your LOOKUP or XLOOKUP formula directly into the formula bar.
- 4. Apply conversion: Press Enter to instantly calculate your currency conversions and drag down to apply to other rows.
Frequently Asked Questions
Why is my LOOKUP formula returning the wrong exchange rate?
The LOOKUP function requires the lookup vector (your dates) to be sorted in ascending order. If they are not sorted chronologically, the formula will return an incorrect or unexpected rate.
Can I use VLOOKUP instead to find exchange rates by month?
Yes, VLOOKUP can be used with the TRUE argument for approximate matches, which finds the closest previous date. However, XLOOKUP is generally preferred as it is more robust and doesn't require the lookup column to be the first column in the table.
How do I extract just the month from a date to match rates?
You can use the MONTH() function to extract the month number (1-12), or the EOMONTH(date, -1)+1 formula to return the first day of the month. The latter is often better as it preserves the year, preventing mismatches between different years.
What if a transaction occurs on a weekend when there are no FX rates?
By using LOOKUP or VLOOKUP with an approximate match mode, the spreadsheet will automatically fall back and find the exchange rate from the closest preceding business day.
