Using VLOOKUP with Multiple Data Sheets

[MJGC]

I’ve been sent about 300 excel files, each with identically situated and formatted data (a 4 by 6 matrix of numbers with labels. I need to gather all of this data into one spreadsheet … 300 rows with the first column containing the name of each file and the next rows containing the contents of the data fields.

I’ve used VLOOKUP a lot but not across so many spreadsheets. My problem is getting the “table array” portion of the VLOOKUP formula to use the values in column a as a variable to go to the different files to pull the data.

For example, let’s say the data files are named apple.xls, banana.xls, orange.xls, etc. In my summary file I have all the names of the files listed in Column A: apple, banana, orange, etc. In column B I have started with the following VLOOKUP formula:

=VLOOKUP(“2001”,[Apple.xls]DATA!$A$1:$E$17,2,FALSE)

so that the formula will search on the second column in the row titled “2001” in the DATA worksheet of the Apple.xls file. But how do I get table array filed in the VLOOKUP formula to vary using the variables in column A so that when I copy the formula down column B it will look to the different spreadsheets?

mjgc

[vane0326]

Say you have a total of 9 worksheets, list those worksheet names in column B in range B2 thru B10. Then input this formula.

Please Login or Register  to view this content.

If this doesn't work post a sample workbook.


The formula is an array need to hold down:

Ctrl,shift,Enter