PDA

View Full Version : vlookup range in another sheet



kiltro
03-22-2018, 02:07 AM
Hello everyone,

I'm trying to use the VLOOKUP function using as table_array a range from another sheet.

Giac = VLookup(SKU, Sheet2!$A$1:$G$20000, 5)
But this gives me error messages, what am I doing wrong?

offthelip
03-22-2018, 02:18 AM
Is SKU defined as a named range? if not it will fail. if you are trying to lookup the text string SKU it should be:

Giac = VLookup("SKU", Sheet2!$A$1:$G$20000, 5)

kiltro
03-22-2018, 02:26 AM
SKU is a variable, defined with


SKU = ActiveCell.Value

offthelip
03-22-2018, 03:52 AM
PErsonally I never use Vlookup in vBA because there is a much faaster and more efficient way of doing it specially if you are doing more than one, so try this code as an alternavtive, it usese a variant array instead:
also it will tell you if it is "not found" instaed of just coming up with an error


inarr = Worksheets("Sheet2").Range("A1:g20000").Value
giac = "Not Found"
For i = 1 To 20000
If SKU = inarr(i, 1) Then
giac = inarr(i, 5)
Exit For
End If
Next i

kiltro
03-22-2018, 04:33 AM
Thanks for the suggestion!

Lets say SKU is first column in Sheet1, with say 1000 values.
I'd like to search every value of SKU column in Sheet2 column A ("A1:A20000"), then if the active value of SKU is present copy the value of the 5th column cell to Giac variable.

how should I update your code?

offthelip
03-22-2018, 04:45 AM
still very simple:



SKU = Worksheets("Sheet1").Range("A1:A1000").Value
Dim giac(1 To 1000) As Variant


inarr = Worksheets("Sheet2").Range("A1:g20000").Value
For j = 1 To 1000
giac(j) = "Not Found"
For i = 1 To 20000
If SKU(j, 1) = inarr(i, 1) Then
giac(j) = inarr(i, 5)
Exit For
End If
Next i



Notice that although you are doing 1000 "vlookups" there is only one access to the worksheet which is loading the variant array inarr. In vba the slowest action is accessing the worksheet which is why looping through a range on the worksheet is very slow while looping through a variant array is very fast.

rlv
03-22-2018, 09:04 AM
Keep in mind that

SKU = activecell.value
Giac = VLookup(SKU, Sheet2!$A$1:$G$20000, 5)
omits the range_lookup parameter which defaults to True for an approximate match. If you want an exact match you would need to explicitly set range_lookup to False

SKU = activecell.value
Giac = VLookup(SKU, Sheet2!$A$1:$G$20000, 5, False)

https://support.office.com/en-us/article/Quick-Reference-Card-VLOOKUP-refresher-750FE2ED-A872-436F-92AA-36C17E53F2EE

kiltro
03-23-2018, 12:43 AM
still very simple:



SKU = Worksheets("Sheet1").Range("A1:A1000").Value
Dim giac(1 To 1000) As Variant


inarr = Worksheets("Sheet2").Range("A1:g20000").Value
For j = 1 To 1000
giac(j) = "Not Found"
For i = 1 To 20000
If SKU(j, 1) = inarr(i, 1) Then
giac(j) = inarr(i, 5)
Exit For
End If
Next i



Notice that although you are doing 1000 "vlookups" there is only one access to the worksheet which is loading the variant array inarr. In vba the slowest action is accessing the worksheet which is why looping through a range on the worksheet is very slow while looping through a variant array is very fast.

Shouldn't I put a Next j and Exit for , j loop too?

offthelip
03-23-2018, 01:49 AM
Yes you need the next j at the end, but not another exit for sorry about that I missed it on the cut and paste

kiltro
03-23-2018, 03:12 AM
Thank you very much, all is working now!