Function Problems

Extract Specific Exchange Rates from STOCKHISTORY Data in Excel

Nimra Malik Oct 8, 2026 869 views

Question details

The user needs to extract a specific USD-to-INR exchange rate for a designated date from a dataset generated by the STOCKHISTORY function.

How to Look Up Specific Exchange Rates from STOCKHISTORY in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Performing financial data analysis that requires extracting exact historical exchange rates based on a specific target date.
Observed behavior: Historical dates are located in column A and their corresponding exchange rates are in column B. The user inputs a target date in cell C2 and expects the matching exchange rate to appear in cell D2.

Before you start

Ensure that both your requested date in cell C2 and the historical dates in column A are formatted as valid Excel serial dates rather than text strings.

Solution 1Recommended

Use the XLOOKUP Function to Find the Exact Rate

XLOOKUP is the most modern and robust method to search for a specific date and instantly return the corresponding exchange rate.

The XLOOKUP function simplifies searching for data both vertically and horizontally. It also includes built-in error handling for dates that might fall on holidays or weekends when no rate is recorded.

Select the destination cell

Click on cell D2, which is where you want the specific exchange rate to be displayed.

Enter the XLOOKUP formula

Type the formula =XLOOKUP(C2, A:A, B:B, "Not found", 0) into the formula bar.

Calculate the result

Press Enter. The function will search for the exact date from C2 within column A and return the matching USD-to-INR rate from column B.

Use the XLOOKUP Function to Find the Exact Rate

Built-in Error Handling: The "Not found" argument safely catches errors if the exact date is missing from your dataset, preventing your spreadsheet from showing standard #N/A errors.

Solution 2

Use INDEX and MATCH for Older Excel Versions

If you are using a legacy version of Excel that does not support XLOOKUP, you can use the classic combination of INDEX and MATCH functions.

WPS Spreadsheet Solutions

Analyze Financial Data Seamlessly with WPS Office

WPS Spreadsheet offers comprehensive support for modern lookup formulas, including XLOOKUP, INDEX, and MATCH. It allows you to efficiently analyze exchange rates and track financial datasets with a highly familiar, fast interface.

1. Open your dataset: Launch WPS Spreadsheet and open the workbook containing your historical exchange rate data.
2. Select the target cell: Click on the destination cell (e.g., D2) where you want the specific date's exchange rate to appear.
3. Input the lookup formula: Go to the Formulas tab, or simply type =XLOOKUP(C2, A:A, B:B) directly into the formula bar.
4. Retrieve the data: Press Enter to execute the function and instantly retrieve the accurate exchange rate for your specified date.

Fully compatible with Microsoft Excel formulas and file formats (.xlsx).Natively supports advanced functions like XLOOKUP for precise financial data matching.Free, lightweight, and features an intuitive interface familiar to Excel users.

Frequently Asked Questions

Why does my formula return an #N/A error even though the date is in the list?

This typically happens due to formatting mismatches. Ensure that both your requested lookup date and the historical dates in column A are formatted strictly as Date values. If one is stored as text or contains hidden time values, the exact match will fail.

Can I look up the next available exchange rate if the exact date falls on a weekend?

Yes. If you are using XLOOKUP, you can change the 'match_mode' parameter to 1 (exact match or next larger item) or -1 (exact match or next smaller item) to automatically find the closest available trading day's exchange rate.

Does the STOCKHISTORY function automatically update historical rates?

STOCKHISTORY pulls financial data from active online sources. While past historical dates remain static since they have already occurred, refreshing the workbook will update current rates or add new daily rates if your parameters include 'Today'.

Excel & Data Problems