Vlookup Using VBA (without using VLOOKUP function)
 

On 9 Nov, 23:16, wrote:
Hello,

I need to do a Vlookup using a key column to grab about 10 columns
from the lookup table (1000 rows). Using the VLOOKUP function turned
out to be very inefficient (10000 cells of VLOOKUP). I was wondering
if this could be accomplished more efffectively using some variation
of looping/Find/replace/VBA etc. Sample code would be greatly
appreciated.

Manish

Why don't you try using a database like Access?

I need to do lookups on very large files quite often, and, trust me,
Access is extremely faster and more reliable at this than Excel. I
also find that exporting to Access, looking up the data in Access and
then importing back into Excel is a very fast and efficient solution.

It is faster to set up the lookup: you just need to set up a join
between 2 tables or queries, and then it's simply a matter of dragging
the required fields into the query you're building.

It is more powerful because you can choose to show only common values,
only mismatches or both.

It is more reliable because you don't have to worry about numbers
formatted as text, multiplying numbers by 1 and all those other
annoying issues that make Excel a very poor tool for data analysis.

Running it is much faster than in Excel.