How to Lookup Exchange Rates in Multiple Yearly Columns in Excel
Question details
The user needs a formula to look up exchange rates based on a specific date when the source data is split into multiple two-column pairs (Date and Rate) for each year.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Retrieving financial or exchange rate data for specific dates when the dataset is organized horizontally across multiple yearly columns instead of a single continuous vertical list.
- Observed behavior
- The lookup fails using standard VLOOKUP because the data ranges are disjointed. The data needs to be virtually combined into a single array before applying a lookup function.
Ensure you are using Microsoft 365 or a version of your spreadsheet software that supports dynamic array functions like VSTACK, XLOOKUP, LET, and CHOOSECOLS.
Use VSTACK and XLOOKUP to Combine and Search Columns
This solution merges multiple yearly ranges into a single virtual array using VSTACK, and then uses XLOOKUP to find the exact or closest preceding exchange rate.
By utilizing the LET function, you can define the combined ranges once to keep the formula clean. VSTACK stacks your separate yearly columns (e.g., A2:B8 for 2020 and C2:D9 for 2021) vertically.
XLOOKUP is then applied to the virtual array. If you need the most recent exchange rate on or before the input date, the match mode can be set to -1.
Click on the cell where you want the exchange rate result to be displayed.
Start your formula by combining the ranges. Type: =LET(FXrate, VSTACK(A2:B8, C2:D9),
Complete the formula by searching your input date against the virtual array: XLOOKUP(A19, CHOOSECOLS(FXrate, 1), CHOOSECOLS(FXrate, 2), "Not found", -1)). Replace A19 with your date input cell.
Press the Enter key. The formula will stack the arrays in the background and return the correct exchange rate for the specified date.
Use the Classic LOOKUP Function for Sorted Dates
If you prefer using the older LOOKUP function combined with dynamic arrays, this method works well as long as your source dates are sorted in ascending order.
Use WPS Spreadsheets for Advanced Dynamic Arrays
WPS Office perfectly supports dynamic array functions like VSTACK, XLOOKUP, and LET. You can easily manage complex financial data, combine split columns, and look up exchange rates without missing a beat.
- 1. Open your dataset in WPS Office: Launch WPS Spreadsheets and open your workbook containing the yearly exchange rate columns.
- 2. Select the result cell: Click the specific cell where you want the exchange rate to be populated.
- 3. Insert the dynamic formula: Type in your =LET(FXrate, VSTACK(...), XLOOKUP(...)) formula exactly as you would in Excel.
- 4. Apply and drag: Press Enter to get the result. If you have multiple dates to look up, drag the fill handle down to apply the formula to the remaining rows.
Frequently Asked Questions
Why does my XLOOKUP formula return a #NAME? error?
A #NAME? error usually indicates that your version of the spreadsheet software does not support one of the functions used, such as VSTACK or LET. Ensure you are using Microsoft 365, Excel 2021, or the latest version of WPS Office.
Can I look up an exchange rate if the exact date falls on a weekend?
Yes. By adding '-1' as the match_mode argument in your XLOOKUP formula, the function will search for the exact date and, if not found, it will automatically fall back to the closest preceding date (e.g., Friday's rate for a Sunday lookup).
What is the CHOOSECOLS function used for in this formula?
Once VSTACK merges your yearly tables into a single block, CHOOSECOLS separates that block into a lookup array (column 1 for dates) and a return array (column 2 for rates) so that XLOOKUP knows exactly where to search and what to return.
How do I combine more than two years of data?
You can add as many ranges as you need inside the VSTACK function separated by commas. For example: VSTACK(A2:B8, C2:D9, E2:F10, G2:H12). It seamlessly stacks all arrays sequentially.
