Use VLOOKUP with a Closed Workbook and Daily File Names in Excel
Question details
Find a reliable way to look up data in a closed workbook whose file name changes daily.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A lookup needs to retrieve data from a daily source workbook without manually changing the file reference each day.
- Observed behavior
- A hard-coded external reference works with the source workbook closed, but references constructed with INDIRECT and CONCATENATE do not reliably retrieve data from closed files.
Confirm the folder path, the daily file-naming pattern, and the sheet or table containing the lookup data. Check that the daily files have a consistent structure and that Excel has Power Query available.
Import the daily workbook with Power Query, then use VLOOKUP
Use a folder query to select the file for the current date and load its data into the lookup workbook.
Excel's INDIRECT function generally requires the referenced workbook to be open. Power Query avoids constructing an INDIRECT reference by importing the selected file's data instead.
In Excel, select Data > Get Data > From File > From Folder. Choose the folder containing the daily workbooks, then select Transform Data.
In Power Query Editor, filter the Name column using the file-naming pattern. For a name such as Sales_2026-06-05.xlsx, add a step using the formula bar: = Table.SelectRows(#"Previous Step", each [Name] = "Sales_" & Date.ToText(Date.From(DateTime.LocalNow()), "yyyy-MM-dd") & ".xlsx"). Replace Previous Step with the actual preceding step name and adjust the prefix and date format to match your files.
Use Combine Files on the filtered Content column, choose the source sheet or table, and select Home > Close & Load. Make sure the lookup column is to the left of the return column in the loaded data.
If the imported data is on a sheet named DailyData, enter a formula such as =IFERROR(VLOOKUP(A2,DailyData!$A:$B,2,FALSE),"Not found") in the destination sheet. Adjust the sheet name, columns, and return-column number to match your data.
Select Data > Refresh All to rerun the query when a new daily file is available. Check that the refreshed query selected the intended file.
Work with lookup tables in WPS Spreadsheet
WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and Microsoft Office format compatibility for an easy move between applications. You can use WPS Spreadsheet to work with lookup data, although it does not fix Excel's INDIRECT limitation or automatically replace the folder-query workflow above.
Frequently Asked Questions
Why doesn't INDIRECT work with my closed source workbook?
Excel's INDIRECT generally requires an external source workbook to be open. A direct external reference can work while the source is closed, but constructing that reference as text with INDIRECT does not provide the same behavior.
How does the query choose a different file each day?
The Power Query filter builds the expected file name from the local date when the query refreshes. Adjust the prefix and date format in the Name filter to match your daily files, then use Data > Refresh All.
What happens if today's file is missing?
The Name filter will return no matching file, and subsequent combine or load steps may fail or return no data. Check the folder, file name, and date, then select Data > Refresh All after the file is available.
Can I use a fixed external VLOOKUP reference instead?
Yes. If the source file name and location stay the same, a direct external reference can retrieve data from a closed workbook. A changing daily name requires the reference to be updated or the data to be imported another way.
