I played around w/a few things to get an idea of calc speed. I set up a
table with ~ 5000 rows x 10 columns of data. Then I randomly chose ~ 4500
entries from the table and put it on Sheet2 and looked up the 4500 items in
the table to return 10 cols of data.

Using the macro (bottom of the post) took about 124 seconds to run. Using
Daves Match/Index suggestion took 3.5 seconds, my vlookup 2.4 seconds.
Playing off of Daves suggestion, I sorted the table, entered an approximate
Match in cell B1
=MATCH($A1,Sheet1!$A$1:$A$5248,1)
and this in C1
=IF(ISNA($B1),"missing",IF(INDEX(Sheet1!$A$1:$A$52 48,$B1)=$A1,INDEX(Sheet1!B$1:B$5248,$B1),"missing" ))
and it computed in 0.25 seconds.

To answer your question, VBA is the slowest except for an exact match
Vlookup from the suggestions so far. If it must be done programmatically
(size of the ranges are unkown until run time), I'll use VBA to determine the
necessary range addresses, then build the XL formulas to do the calculations,
and copy/edit/paste special if necessary.

Sub test()
Const lngColsToReturn As Long = 10
Dim rngKey As Range
Dim rngLookupValues As Range
Dim rngCell As Range
Dim rngFound As Range

Set rngKey = Sheet1.Range("A:A")
Set rngLookupValues = Sheet2.Range("A1:A4622")

For Each rngCell In rngLookupValues.Cells
Set rngFound = rngKey.Find( _
what:=rngCell.Value, _
after:=rngKey.Range("A1"), _
LookIn:=xlValues, _
lookat:=xlWhole, _
searchorder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False, _
matchbyte:=False)
If Not rngFound Is Nothing Then
rngCell(1, 2).Resize(1, lngColsToReturn).Value = _
rngFound(1, 2).Resize(1, lngColsToReturn).Value
Set rngFound = Nothing
End If
Next rngCell

End Sub
"Dave Peterson" wrote:

I would think that a VBA approach would take longer than formulas. But if you
don't need the formulas, you could always edit|copy, edit|paste special|values.



wrote:

On Nov 10, 2:12 am, JMB wrote:
Another approach, if you can sort the lookup table based on the key column,
you could use multiple approximate match lookups to perform an exact match
lookup, which are faster using a single exact match lookup.

To look up an Item in your Table and return the data from the second column:
=If(Isna(Vlookup(Item, Table, 1, 1)),"missing",If(Vlookup(Item, Table, 1,
1)=Item, Vlookup(Item, Table, 2, 1), "missing"))



" 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- Hide quoted text -

- Show quoted text -

I was hoping for a non-formulaic approach (VBA??) which would store
the returned values as values and not a formula.

--

Dave Peterson