5,256,000 cells

[msjones]

So, I'm not an Excel god...more like an Excel go-to-person by default. I've been banging my head trying to get the following:

One row (say row 6) to get it's data from a whole other workbook and row. I have been using the VLOOKUP option but once I start doing that for each cell, Excel starts yelling at me that it's TOO much factoring...more or less. For each worksheet it would be 8,760 cells of VLOOKUP. OYE!

What I WANT and need to know if it is possible is:
Look up the value of cell D1 compare it to column A1-A11200 (yes 11thousand) on a whole OTHER workbook. Find THAT number then return the whole rows worth of data (B#:BC#)back to my origional worksheet/book filling (B#:BC#)

Is there such a formula or something out there to save me from my Excel hate...?

 

[Joe4]

You might have more using a database program such as Access, where your can link your different tables (workbooks).

For doing things such as matching on a large scale, database programs like Access usually work much better (they were designed for the "relational" nature of multiple data tables).

 

[msjones]

Ya...I love Access...even then (because of the way the data operates) I would have STILL need the inital tables set up (populate the fields) which is what I am running into.

I serriously do not want to copy paste onto 600 workbooks the rows of unique data I need from the 120 other workbooks. When each of those 120 workbooks have 11,000 rows in them. And all I need are the specific 600 rows from each one...are you seeing me using the 'find' button 600x120=72,000 times? THIS is why I want the 'cheat'/formula to go FIND those rows I need.

 

[Aladin Akyurek]

Are you willing to experiment with the following set up?

Sort the data area in each workbook on column A, that is, on the match range.

In the destination sheet...

D1 is the lookup value.

E1:

=IF(LOOKUP(D1,[WB.xls]Sheet1!$A$1:$A$11200)=D1,MATCH(D1,[WB.xls]Sheet1!$A$1:$A$11200,1),"")

F1. copy across then down:

=IF(N($E1),INDEX([WB.xls]Sheet1!B$1:B$11200,$E1),"")