Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question

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:


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

share|improve this answer
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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .