vlookup range in another sheet

[kiltro]

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]

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]

SKU is a variable, defined with

SKU = ActiveCell.Value

[offthelip]

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]

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]

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]

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/art...A-36C17E53F2EE

[kiltro]

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]

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]

Thank you very much, all is working now!