MATCH() or VLOOKUP() across mutliple 65K sheets possible?

[EagleOne]

2003

Imported a large file into 3 Excel w/s. The w/s are identical in format.

Is there a way to perform a VLOOKUP() or MATCH() across three 65K sheets?

ANY other ideas?

TIA EagleOne

 

[Jim Thomlinson]

You can't really do it and even if you could you would grind the system to a
halt trying. A database will work much better for you in this case. XL 2007
will handle that much data but once again you really would be better off with
a database.

 

[EagleOne]

Thanks for the reply.

Does Access have a analogous function or workaround?

As Access databases are not in memory, I assume that "the function" would be in the form of a query?
What concerns me is the that I would have to run that query 200,000 times at which time I'll grind
to a halt. What am missing? It is OK to hit me with a 2x4.

 

[Pete_UK]

Here's a generic approach across 3 Excel sheets:

=IF(ISNA(match_1),IF(ISNA(match_2),IF(ISNA(match_3),"no
match",match_3),match_2),match_1)

where match_1 will look something like MATCH(A1,Sheet1!A:A,0), and
match_2 will be MATCH(A1,Sheet2!A:A,0) and so on, so these relate to
the different sheets. You could use VLOOKUP instead, so vlookup_1
would look like VLOOKUP(A1,Sheet1!A:B,2,0). You will get the message
"no match" if none of the records in the 3 sheets match with A1.

Hope this helps.

Pete

 

[Jim Thomlinson]

In Access you would just use a join of two tables or you could use a query to
return a recordset of matching records. Depends what you are up to. In the
grander scheme of thing vlookup or match are intended to relate records
together. Databases are relational in nature and are optomised to relate
records together. XL are essentially flat files. You will not grind Access to
a halt with 200k records. You could have millions of records with out to much
difficulty...