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?

-

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

-
If values are text (and not blank), you can change the formula in `B2` to `=VLOOKUP("*", F1:F3, 1, FALSE)`, which is shorter and not an array formula (doesn’t require Ctrl+Shift+Enter). If the values are not text, use `=INDEX(F1:F3, MATCH(FALSE,ISERROR(F1:F3),0), 0)` (with Ctrl+Shift+Enter). If the values are dates (e.g., Index = Person’s Name and Value = Date of Birth), format cell `B2` as Date. – Scott Oct 26 '12 at 20:45
@Scott, Thanks for the tip on using "*" in `VLOOKUP`, it's indeed much nicer. – Michael Oct 27 '12 at 8:16