Vlookup VBA Alternative

[helpexcel]

Is there a quicker way to use vlookup with VBA. I have a lot of data that I would just use vlookup with, across multiple sheets. Hoping there's a way to speed things up. THANKS!

 

[offthelip]

@helpexcel
my mistake I left out a next KK

If inarr(j, 1) = Searchfor Then
 For kk = 2 To 18
outarr(i, kk - 1) = inarr(j, kk)
[COLOR=#ff0000] next kk[/COLOR]
Exit For
End If

 

[offthelip]

@PietBom
I totally agree with your analysis, I hadn't thought about the time taken to copy the array. One of the things that might be slowing this down is running into limits of memory with a 1000 copies of the array
Undoubtedly the fastest way to do 1000 vlookups would be to use your binary search method but in a straight VBa solution which loads the array just once.
I might think of doing that if I get a super large problem. Thanks for sharing your code

 

[helpexcel]

I should've caught that one, sorry! but it works great, THANK YOU!!!!

 

[helpexcel]

Trying to adapt this code for the following: All of my data is on Sheet1 A3:Z & Lastrow. I filtered Sheet1 Column J and moved the results to Sheet2 Column J. I now want to fill in all the data in Columns:Rows A:I & K:Z.

This is what I'm using for the A:I part:




Dim lastrow, lastrow2, i As Long
Dim Searchfor, j, inarr As Variant


'Data Dump Sheet
With Sheets("Sheet1")
lastrow = .Cells(Rows.Count, "J").End(xlUp).Row
inarr = Range(.Cells(3, 1), .Cells(lastrow, 26))
End With




'Values to look up & paste Sheet
With Sheets("Sheet2")
lastrow2 = .Cells(Rows.Count, "J").End(xlUp).Row
' load variant array with sercha variables
searcharr = Range(.Cells(3, 10), .Cells(lastrow2, 10))
' define an output aray
outarr = Range(.Cells(3, 1), .Cells(lastrow2, 9))
End With




On Error Resume Next
For i = 3 To lastrow2
For j = 3 To lastrow
Searchfor = searcharr(i, 1)
If inarr(j, 1) = Searchfor Then
For kk = 1 To 10
outarr(i, kk - 1) = inarr(j, kk)
Next kk
Exit For
End If
Next j
Next i
' writeout the output array
With Sheets("Sheet2")
Range(.Cells(3, 1), .Cells(lastrow2, 9)) = outarr
End With

 

[helpexcel]

had the sheets backwards!

 

[jflow]

I've tried modifying this code to my purposes, I am looking for X amount if vehicles out of 1000s vehicles.
The issue I am run into is the values that are returned are the first output of the reference page not the specific referenced vehicles.

Sub Test()

Dim lastrow, lastrow2, i As Long
Dim Searchfor, j, inarr As Variant

'Output Sheet
With Worksheets("Sheet1")
lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
inarr = Range(.Cells(2, 2), .Cells(lastrow, 7))
End With

'Reference Sheet
With Worksheets("Jan19_R")
lastrow2 = .Cells(Rows.Count, "A").End(xlUp).Row
searcharr = Range(.Cells(3, 1), .Cells(lastrow2, 1))
outarr = Range(.Cells(3, 2), .Cells(lastrow2, 7))
End With



On Error Resume Next
For i = 2 To lastrow2
For j = 3 To lastrow
Searchfor = searcharr(i, 1)
If inarr(j, 1) = Searchfor Then
For kk = 2 To 6
outarr(i, kk - 1) = inarr(j, kk)
Next kk
Exit For
End If
Next j
Next i

With Worksheets("Sheet1")
Range(.Cells(2, 2), .Cells(lastrow, 7)) = outarr
End With


End Sub

 

[PietBom]

@jflow,
I tested your code on my 1.000.000 records table.
It took 1,5 minutes to search 1.000 codes.
As the result of new insights I now have a much faster lookup routine:
It searches the 1.000 codes in the 1.000.000 records table in less than 0.5 secondes.
It looks unbelievable but you should try it yourself.
This is the formula:

=IF(VLOOKUP(B2,Table1,[COLOR=#0000cd]1[/COLOR],[COLOR=#ff0000]1[/COLOR])=B2,VLOOKUP(B2,Table1,[COLOR=#0000cd]2[/COLOR],[COLOR=#ff0000]1[/COLOR]);NA())

The principle is as follows:
First you test if the search code exists in Table1 with the VLOOKUP formula using 1 for the 4th parameter.
This is a super fast search routine in a alphanumeric sorted index.
If there is a match then you apply the VLOOKUP to search a specific column.
If there is no match the result will be #N/A
It does not look very efficient to do 2 times a VLOOKUP run, but this variant is approx. 1000 times faster then the sequencial VLOOKUP (4th parameter is 0)
Try it, you love it

 

[jflow]

@PietBom

I appreciate your response, I already use an index match function to find the data. I am trying to learn how to use VBA so that is why I am going about the process in the manner I posted above. This is more about personal development over efficiency.

 

[helpexcel]

@offthelip I'm using this code for something else and for the life of me i can't figure out why its not working.

I'm looking up data on sheet16 columns A:D starting in row 8. I'm looking to match sheet16 Column A with Column B of Sheet1. Then put Sheet16 columns C:D in Sheet1 Columns D:E.


Sub Test3()


Dim lastrow, lastrow2, i As Long
Dim Searchfor, j, inarr As Variant


Set ws1 = Sheet1
Set ws16 = Sheet16


'Data Dump Sheet
With ws16
lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
inarr = Range(.Cells(8, 1), .Cells(lastrow, 4))
End With


'Values to look up & paste Sheet
With ws1
lastrow2 = 31 '.Cells(Rows.Count, "A").End(xlUp).Row
' load variant array with sercha variables
searcharr = Range(.Cells(7, 2), .Cells(lastrow2, 2))
' define an output aray
outarr = Range(.Cells(7, 4), .Cells(lastrow2, 5))
End With


On Error Resume Next
For i = 7 To lastrow2
For j = 8 To lastrow
Searchfor = searcharr(i, 1)


If inarr(j, 1) = Searchfor Then
For kk = 3 To 4
outarr(i, kk - 1) = inarr(j, kk)
Next kk
Exit For
End If


Next j
Next i


' writeout the output array
With ws1
Range(.Cells(7, 4), .Cells(lastrow2, 5)) = outarr
End With






End Sub

 

[offthelip]

your problem is that you are defining your arrays starting at rows 7 or 8 ,

inarr = Range(.Cells(8, 1), .Cells(lastrow, 4))

searcharr = Range(.Cells(7, 2), .Cells(lastrow2, 2))

then you are starting you loop from 7 or 8 to last row:

For j = 8 To lastrow

For i = 7 To lastrow2

this means that what you are doing is starting the check from rows 14 and 16 !! which obviously isn't going to work.
the simplest way round this is to define all of your arrays starting at row one, recognising that you will only start checking from row 7 or 8:

inarr = Range(.Cells(1, 1), .Cells(lastrow, 4))



searcharr = Range(.Cells(1, 2), .Cells(lastrow2, 2))


outarr = Range(.Cells(1, 4), .Cells(lastrow2, 5))


Range(.Cells(1, 4), .Cells(lastrow2, 5)) = outarr

the only time this doesn't work is if you have equations in the first 7 rows of columns D and E of sheet 1. ( if it is just text it copies it and write it back again. ie. nothing changes)