How to Retrieve Data by Date in Excel Using XLOOKUP
Question details
The user wants to retrieve an entire row of data from a source sheet into an overview dashboard based on a dynamically selected date.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Building a dynamic dashboard where selecting or changing a date in a specific cell automatically pulls and displays the corresponding data from an input sheet.
- Observed behavior
- By selecting a date in the overview sheet, the formula automatically searches the date column in the input sheet and populates the adjacent data across multiple columns.
Ensure that the dates in both the input sheet and your overview sheet are identically formatted as Dates, as mismatched formats (like Text vs. Date) or hidden time values will cause lookup errors.
Use the XLOOKUP Function (Microsoft 365 & Excel 2021)
XLOOKUP is the most efficient and modern method to find a specific date and dynamically return multiple columns of corresponding data.
The XLOOKUP function simplifies data retrieval by allowing you to specify a lookup array and a return array without needing column index numbers. It natively supports returning multiple columns (an array) at once.
Click on the cell in your Overview sheet (e.g., A2) where you want the retrieved data to begin displaying.
Type =XLOOKUP(B1, InputData!A:A, InputData!B:D, "Not Found") into the formula bar. In this formula, B1 is your selected date, InputData!A:A is the column containing dates, and InputData!B:D contains the data columns to return.
Press Enter. If the date is found, the entire row from columns B through D will automatically spill into the adjacent cells.
Use INDEX and MATCH Functions (Older Excel Versions)
If you are using an older version of Excel that does not support XLOOKUP, you can combine INDEX and MATCH to achieve the same result.
Retrieve Data Automatically with WPS Spreadsheet
WPS Spreadsheet offers comprehensive support for modern lookup formulas like XLOOKUP, allowing you to build dynamic, date-based dashboards seamlessly and for free.
- 1. Open your workbook: Launch WPS Office and open your spreadsheet containing the date logs.
- 2. Select the lookup cell: Click on the cell in your dashboard where you want to retrieve the date-specific data.
- 3. Input the formula: Type =XLOOKUP( and select your target date cell, the lookup date column, and the return data range.
- 4. Complete the retrieval: Press Enter to instantly pull the matching data row across your sheet.
Frequently Asked Questions
Why does my XLOOKUP formula return a #N/A error for dates?
A #N/A error usually means the exact date could not be found. Check if the lookup value and the source column have different formats (e.g., one is formatted as Text and the other as a true Date). Additionally, ensure there are no hidden time stamps attached to the dates.
Can I return multiple columns with a single XLOOKUP formula?
Yes. By selecting a multi-column range for the return array (such as InputData!B:D), XLOOKUP will automatically spill the results across adjacent columns without needing to drag the formula.
What is the difference between XLOOKUP and VLOOKUP for date retrieval?
XLOOKUP is much more flexible than VLOOKUP because it can look up values in any column (left or right of the data), natively handles exact matches by default, and can return multiple columns at once without requiring a specific column index number.
Will my formula update automatically when new data is added?
Yes, if you reference entire columns (like A:A or B:D) in your formula, or if your source data is formatted as an official Excel Table, the formula will automatically include any newly added dates and corresponding data rows.
