Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

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

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.