How to Return Correct Exchange Rate by Date and Currency in Excel
Question details
The user needs to automatically retrieve the correct exchange rate based on a specific order date and payment currency when the exchange rate tables are stored in separate ranges.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Looking up dynamic financial data from multiple separate ranges depending on a specified currency and date condition.
- Observed behavior
- Exchange rates are successfully populated for each order based on the matching date and currency conditions.
Ensure that your separate exchange rate tables are clearly structured and that your date formats match between your order list and your lookup tables to prevent #N/A errors.
Use IF, INDEX, and MATCH Functions
This method nests INDEX and MATCH inside an IF statement to evaluate the currency first, and then looks up the date in the appropriate currency table.
By combining the IF function with INDEX and MATCH, you can create a dynamic formula that checks the requested currency. Based on that currency, the formula directs the lookup to the correct table, finding the specific date and returning the corresponding exchange rate.
Click on the cell where you want the correct exchange rate to be displayed.
Input the formula: =IF(C2="USD",INDEX($L$2:$L$16,MATCH(B2,$J$2:$J$16,0)),INDEX($H$2:$H$16,MATCH(B2,$F$2:$F$16,0))) into the formula bar. Replace C2 with your currency cell, B2 with your date cell, and the absolute ranges with your specific lookup tables.
Press Enter to execute the formula, then click and drag the fill handle down to apply this logic to the rest of your orders.
Use LET, XMATCH, and Multiple-Area INDEX
For users with modern spreadsheet environments supporting dynamic arrays, using LET alongside XMATCH simplifies handling multiple currency tables.
Easily Manage Financial Data and Formulas with WPS Spreadsheet
WPS Spreadsheet provides robust support for complex financial formulas, including IF, INDEX, MATCH, and modern functions. You can easily build automated exchange rate lookup tables to streamline your workflow with full compatibility.
- 1. Open your financial dataset: Launch WPS Office and open your workbook containing the order dates, payment currencies, and exchange rate tables.
- 2. Apply the multi-condition lookup: Select the target cell and type your IF, INDEX, and MATCH combination to pull the correct exchange rates from your distinct ranges.
- 3. Manage data efficiently: Use the built-in Formula tab and Name Manager in WPS Spreadsheet to keep track of your currency tables and ensure error-free calculations.
Frequently Asked Questions
What if my exact order date is not in the exchange rate table?
If the exact date is missing, you can change the match_type parameter in the MATCH function from 0 (exact match) to 1 (less than) or -1 (greater than). Keep in mind that doing this requires your exchange rate tables to be sorted chronologically.
How can I handle errors if the currency or date is not found?
You can wrap your entire lookup formula inside an IFERROR function, such as =IFERROR(your_formula, "Rate Not Found"). This will display a clean, custom text message instead of an ugly #N/A error.
Can I use VLOOKUP instead of INDEX and MATCH for this scenario?
Yes, but it requires nesting multiple VLOOKUPs inside an IF function. INDEX and MATCH is generally preferred for this scenario because it provides more flexibility, better performance, and prevents formulas from breaking if you insert new columns.
Why is my INDEX MATCH formula returning an #N/A error?
An #N/A error usually means the exact lookup value (like the date formatting or currency spelling) does not match exactly in your source range. Check for invisible trailing spaces in your text cells and ensure both date columns share the exact same Date formatting.
