vlookup help

[Guest]

I have a worksheet with over 30K records.

I am using a Vlookup in Sheet A , for 4 columns and then dragging it down.
(totallly 120K apprx)

It looks something like this in the Macro :
"=VLOOKUP(C[-2],acc!C[-5]:C[7],3,FALSE)"

however, It slows the application a lot.

Is there an alternative?

 

[Dave Peterson]

You may find it easier to add a column to be used for the index into that
column.

For instance (your ranges will vary):
=match(a2,acc!a:e,0)
(say in C2)
This will return the matching row.

Then use that in the next 4 columns:
In D2:
=index(acc!b:b,c2)
In E2:
=index(acc!c:c,c2)
in F2:
=index(acc!d:d,c2)
in g2:
=index(acc!e:e,c2)

Since you're only doing the matching once, you'll find that it's a little
quicker. Maybe even acceptable???

I used A1 reference style--you'll have to modify those formulas if you're using
R1C1 reference style.

I have a worksheet with over 30K records.

I am using a Vlookup in Sheet A , for 4 columns and then dragging it down.
(totallly 120K apprx)

It looks something like this in the Macro :
"=VLOOKUP(C[-2],acc!C[-5]:C[7],3,FALSE)"

however, It slows the application a lot.

Is there an alternative?