I am trying to
VLOOKUP reference data with around 400 seperate Excel files.
Is it possible to do this in a quick way rather than doing it manually?
Here’s an example solution. It will be somewhat hard to maintain if the reference files are changing frequently, but it’s better than nothing.
Assume we have 3 files,
Create a new workbook,
There you will need to prepare a list of all your file names (let’s say column E).
Add this formula into cells
(again using Ctrl+Shift+Enter). Now, by entering some index into
Note: this assumes that the values are text (and not blank), although it does not assume that the indexes are numeric. If the indexes are not unique, you will get the first match, searching the files in the order that they are listed in column
If needed, it can be modified for different ranges in each file or different worksheets, but this is the idea.