Excel 2007 : multiple Vlookups over multiple workbooks

[Karroog]

Trying to setup a vlookup function that will be able to call information from multiple workbooks.

There is a total of 26 workbooks, lets just call them book1 - book26

These workbooks have thousands of lines of information with 50 or so columns of information that need to be processed. I'm trying to narrow it down so it will lookup the certain columns of information that I wish to see in a compressed and easy to read way. For example, one piece of information I'm trying to lookup is if the item is labeled in one of the columns as "Domestic".

The one spreadsheet in question that I'm trying this on has 1155 rows and around 350 instances of "Domestic".

This is where it gets a bit more complex and I knew immediately I would need assistance. Since I'm trying to obtain this information from multiple workbooks I am having issues setting up a formula. This is what I have so far.

=IF(ISNA(VLOOKUP(A2,'[book1.xlsx]book1'!$A$2:$A$1156,1,0))),VLOOKUP(A2,'[book1.xlsx]book1'!$A$2:$A$1156,1,0)

I had a thought to put the information I wished to call the value of in a different sheet, but then it would get a bit more complex:

=IF(ISNA(VLOOKUP(Sheet2!A2,'[book1.xlsx]book1'!$A$2:$A$1156,1,0))),VLOOKUP(Sheet2!A2,'[book1.xlsx]book1'!$A$2:$A$1156,1,0)

This keeps popping up an error and I'm not sure why.

As I said earlier, this will then extend to Vlookup multiple workbooks, book1 thru book26, to obtain the same information. I found a good example online that shows the basics of what I'm trying to do:

=IF(ISNA(VLOOKUP(A2,Wrk1!$F:$G,2,0)),
IF(ISNA(VLOOKUP(A2,Wrk2!$F:$G,2,0)),
IF(ISNA(VLOOKUP(A2,Wrk3!$F:$G,2,0)),
VLOOKUP(A2,Wrk3!$F:$G,2,0)),
VLOOKUP(A2,Wrk2!$F:$G,2,0)),
VLOOKUP(A2,Wrk1!$F:$G,2,0))

That would actually extend 26 times.

If anyone has any insight on how handle this that would be great.

There is another step that I believe is even more complex that I am also having trouble with as well, but if I can figure out this first step that would be awesome and then I can ask the 2nd question in another thread.

Thanks!!

P.S. - I hope I am explaining this clearly, if not I'll do my best to clarify any information that might be confusing.

[Richard Buttrey]

Hi,

I'm not clear if you're really wanting to look up a single value from somewhere amongst 26 workbooks, or whether you need to sum the data for a value across all the workbooks.

Can you clarify please.

Rgds

[Karroog]

My apologize, let me clarify.

The example I gave, spreadsheet with 1155 rows of data, has a column that is populated as either "domestic" or "international". All 26 workbooks have this same column and are populated as either domestic or international.

I'm trying to get the vlookup down for a single value so I can work on the more complex issue. Eventually I am going to be trying to lookup multiple values from different columns of data within the 26 workbooks.

Let me try to explain in simpler terms, because even I sometimes can't follow it! Let’s just say I have 3 columns:

column 1 column 2 column 3
1 x a e
2 y b e
3 x a e
4 y b f
5 x a f
6 y b f
7 x a f
8 y a f

Each of those columns has a certain value attributed to it. In my workbooks there are 50 columns like this.

What I would be trying to accomplish with this lookup is to, for example, populate the new spreadsheet with every instance that x, a, and e were in the same row. For this shorthand example, I would expect the Vlookup to only grab rows 1 and 3.

The end goal would be to grab the rows of information that my vlookup would try to find, those actual values are Domestic; Phase 3; and Template, but then with those rows of information break it down to about 13 columns that I wish to use for this specific spreadsheet. So I would only be using Vlookup to find those 3 bits of information that occur in the same rows, and then populate the about 13 different columns that contain those 3 specific information within the said columns.

I hope that clarified it a bit better, I apologize if I might have confused everyone more.

[Richard Buttrey]

Hi,

In that case since you're looking to extract many results a VLOOKUP is no good since it only returns one value. I suspect you need to be filtering data and given 26 workbooks almost certainly you'll need to automate this with a macro.

Difficult to comment with certainty unless we can see the request in the context of the actual workbooks.

Rgds