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

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.

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

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

On Nov 10, 5:40 pm, JMB wrote:
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- Hide quoted text -

- Show quoted text -

Thanks for an exhaustive explanation of the approaches. My issue is
that the lookup table may not contain the key being looked up so
approximate VLOOKUP can't be used since it would not return N/A values
which I need. That leaves only the match/index or VBA option. I am
assuming match/index would be faster. I could use XL down command to
ascertain last row based on an adjacent filled-in column.

I would use Index/Match over VBA.

Just to clarify the approximate match issue you raise, using this example
(for your own future reference):
=If(Isna(Vlookup(Item, Table, 1, 1)),"missing",If(Vlookup(Item, Table, 1,
1)=Item, Vlookup(Item, Table, 2, 1), "missing"))

Approximate match lookups will return N/A if the first item in the table is
greater than the item being looked up. Hence the first test
If(Isna(Vlookup(Item, Table, 1, 1)),"missing"........)

If the item does not exist, the lookup finds the largest value that is
smaller than the Item being looked up. That is why I have a second test to
determine if the key from the table that it matched the item to is equal to
the item being looked up by returning the first column from the table:
If(Vlookup(Item, Table, 1, 1)=Item, Vlookup(Item, Table, 2, 1), "missing")

If an exact match is found (data returned from the first column of the table
= Item), it will perform the lookup and return the entry from the second
column - which is the actual data that I want:
Vlookup(Item, Table, 2, 1)

Same concept for the approximate match INDEX/MATCH approach. In this
fashion, approximate match lookup functions can be made to perform exact
matches, but often many times faster than an exact match lookup. But your
approach depends on whether or not you are able to sort the table, can set up
a helper column, whether or not you intend to hardcode the data after the
lookup is finished (no sense in spending 5 minutes to sort and set up helper
columns to save 3 minutes of calculation time), and how often the process
needs to be repeated.

If you want the cells to actually dispaly #N/A instead of "missing" if the
item does not exist in the *sorted* table:
=If(Vlookup(Item, Table, 1, 1)=Item, Vlookup(Item, Table, 2, 1), NA())








" wrote:

On Nov 10, 5:40 pm, JMB wrote:
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- Hide quoted text -

- Show quoted text -

Thanks for an exhaustive explanation of the approaches. My issue is
that the lookup table may not contain the key being looked up so
approximate VLOOKUP can't be used since it would not return N/A values
which I need. That leaves only the match/index or VBA option. I am
assuming match/index would be faster. I could use XL down command to
ascertain last row based on an adjacent filled-in column.