Vlookup Using VBA (without using VLOOKUP function)

  • Thread starter Thread starter web2
  • Start date Start date
W

web2

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
 
If you're returning 10 columns, you may find it less intensive to dedicate a
single column to return an index into that matching column. Then use index that
to retrieve each column you want.

That way, there's only one formula (per row) that's really looking for the
match.

I'd insert a new column (say column X) with a formula like:
=match(a2,sheet2!a:a,0)

Then retrieve the other values with formulas like:
=if(iserror($x2),"missing",index(sheet2!b:b,$x2))
or
=if(iserror($x2),"",index(sheet2!b:b,$x2))
 
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"))
 
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"))







- Show quoted text -
Click to expand...

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.
 
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$5248,$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
 
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$5248,$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 determinethe
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






- Show quoted text -
Click to expand...

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())
 
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
Click to expand...

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

using vlookup in vba 10
Vlookup Function 1
Update link on vlookup from closed excel file 2
Vlookup error 5
VBA using VLookUp 1
transformed vloopup 2
Vlookup in vba - how to use absolute rows not relative 1
Another Vlookup for VBA question 4

Back
Top