Any way to speed up Vlookup?

[John]

Does anyone have any suggestion on how to speed up a workbook that
utilizes vlookup's that require about 6 other workbooks to be open?

I will eventually build an access database to supplant the vlookup,
but in the mean time is it possible to speed it up maybe in using a VB
function that opens the workbooks in a particular way?

TIA,
John

 

[Dave Peterson]

If you have multiple columns being returned by multiple formulas, maybe instead
of this:

=vlookup(a1,sheet2!$a$1:$F$9999,2,false)
=vlookup(a1,sheet2!$a$1:$F$9999,3,false)
=vlookup(a1,sheet2!$a$1:$F$9999,4,false)
(going across columns)

=match(a1,sheet2!$a$1:$A$9999,0)
(in a new column (say E) to return a row into that column. If there isn't a
match, you'll get an error.)

then the other formulas could be reduced to:
=if(iserror(f1),"Missing",index(sheet2!$b$1:$b$9999,e1))

Then you're only looking for the match once.