How do i find a value in column G of another sheet using vlookup?

Question

I am working on a sheet called "Master Filtered" in which the values of column K should be filled with a lookup value from sheet "Ndex Positions" column G.

This is the piece of code I wrote

Sub MTL_OR_TOR()
Dim AcctNb As String
Dim result As String
Dim myRange As Range


Dim lastrow As Long
lastrow = Worksheets("Master Filtered").Cells(Rows.Count, 1).End(xlUp).Row

Worksheets("Master Filtered").Range("K3").Value = "MTL OR TOR"

Set myRange = Worksheets("Ndex Positions").Range("A2:G9000") 'Range in which the table MTL or TOR should be entered


For G = 4 To lastrow

AcctNb = Worksheets("Master Filtered").Cells(G, 3).Value
result = Application.WorksheetFunction.VLookup(AcctNb, myRange, 7, False)

Worksheets("Master Filtered").Range("K" & G).Value = result
Next

End Sub

But this does not seem to work ("Unable to get the Vlookup property of the WorksheetFucntion class")

I don't know what is my error. If that helps, when I substitues the number 7 by the number 2 in the following line: "result = Application.WorksheetFunction.VLookup(AcctNb, myRange, 7, False) " everything works perfectly. But it is taking values from column B rather than column G. That's why I replaced the 2 by a 7 (index of letter G in the alphabet). However, this does not seem to work.

Please let me know if you have any idea where my error could possibly be.

Thanks :)

Cyril Cyril · Accepted Answer · 2019-05-29T20:12:58

Try using Index/Match to be make exact references...

Application.Index(OutputRange, Application.Match(LookupVal, LookupRange,0))

With your data, assuming I get the correct Match range (assuming you find your AcctNb in column A):

AcctNb = Worksheets("Master Filtered").Cells(G, 3).Value
With Worksheets("Ndex Positions")
    Application.Index(.Range("G:G"), Application.Match(AcctNb, .Range("A:A"),0))
End With

Note the 0 in the Match means "exact match"

How do i find a value in column G of another sheet using vlookup?

1 Answers