How can I VLOOKUP in multiple Excel documents?

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?

Solution 1:

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, 1.xslx, 2.xslx and 3.xslx, and each of them has a table of Index and Value in columns A and B, like this:

Index   Value
  1       a
  2       b
  3       c

Create a new workbook, index.xlsx, to look something like this:

      A        B        ...        E        F
1  index:                        1.xlsx
2  value:                        2.xlsx
3                                3.xlsx

There you will need to prepare a list of all your file names (let’s say column E). Add this formula into cells F1:F3 (select the range, enter the formula, and type Ctrl+Shift+Enter):

=VLOOKUP($B$1, INDIRECT("["&$E$1:$E$3&"]Sheet1"&"!$A$2:$B$5"), 2, FALSE)

replacing 3 with the number of files (i.e., the number of entries in column E) and 5 with the highest row number in any of your files. If you choose not to have headings in row 1 of your reference files, change $A$2 to $A$1. The second 2 refers to the fact that we are pulling data from column B of the reference files.

In cell B2, enter the formula:

=INDEX(F1:F3, MATCH(TRUE,ISTEXT(F1:F3),0), 0)

(again using Ctrl+Shift+Enter). Now, by entering some index into B1, you will have its value from all of the files appear in B2.

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 E.

If needed, it can be modified for different ranges in each file or different worksheets, but this is the idea.

(Here is an example of the solution presented above (called index.xlsx above), for reference.)