VBA VlookUp for large datasets

[Fatman003]

I need to create a VBA VlookUp that will go through Col A to H in a Sheet2 tab(the table_array), match it with column V(look_up value) in Sheet 1 and put the matching results(which is the column index and its in column 7) in cell AD. I was able to test this for a very small range of values, however it doesnt work for my whole datasets. Column V has over 15000 cells! Below is my code so far but it returns an error. Please help

Sub ADDCLM()
Dim table_Row as Long
Dim table_Clm as Long


Table1 = Sheet1.Range("V:V")
Table2 = Sheet2.Range("A:H") 


New_Row = Sheet1.Range("AF2").Row
New_Clm = Sheet1.Range("AF2").Column


For Each c1 in Table1
    Sheet1.Cells(New_Row, New_Clm) = Application.WorksheetFunction.VLookup(c1, Table2, 7, False)
    New_Row = New_Row + 1
Next c1
End Sub

In Excel, this is how the formula looks like: e.g for Cell AF2 ```=VLOOKUP(V2;Sheet2!A:H;7;FALSE)```

 

[Fatman003]

Here's another way...

Sub test()

    Dim table2 As Range
    Set table2 = Sheet2.Range("A:H")


    Dim lastRow As Long
    With Sheet1
        lastRow = .Cells(.Rows.Count, "V").End(xlUp).Row
        With .Range("AD2:AD" & lastRow)
            .FormulaR1C1 = "=VLOOKUP(RC22," & table2.Address(, , xlR1C1, True) & ", 7, False)"
            .Value = .Value
        End With
    End With
    
End Sub

Notice that this avoids looping.

Hope this helps!

Hey guys, I was able to get @Domenic code to work after changing the table2 variable to be

 table2 = Sheets("RTD Data").Range("A:H")

My other problem is how I can also incorporate other columns. I have another column (AE) where the column index is 8; a column where lookup_value is col S and the column_index are 7 and 8.

So in summary i have:
(i) New Column AE = VLOOKUP(V2;Sheet2!A:H;8;FALSE) #col_index is 8
(ii) AF = VLOOKUP(S2;Sheet2!A:H;7;FALSE) #S is the look_up value, column_index is 7
(iii) AG = VLOOKUP(S2;Sheet2!A:H;8;FALSE) #S - lookup_value, col_index - 8
(iii) AH = VLOOKUP(V2;Sheet2!A:H;5;FALSE) #V - look_up value, col_index -5
(iv) AI = VLOOKUP(V2;Sheet2!A:H;6;FALSE) #V - look_up value, col_index - 6

I want this to be incorporated into a single macro. Thanks a lot for the help

 

[Fluff]

How about

Sub Fatman()
    Dim Ary As Variant, Nary As Variant
    Dim i As Long
    
    Ary = Sheets("RTD Data").Range("A1").CurrentRegion.Value2
    With CreateObject("scripting.dictionary")
        For i = 2 To UBound(Ary)
            .Item(Ary(i, 1)) = Array(Ary(i, 7), Ary(i, 8), Ary(i, 5), Ary(i, 6))
        Next i
        Ary = Sheet1.Range("S2", Sheet1.Range("V" & Rows.Count).End(xlUp)).Value2
        ReDim Nary(1 To UBound(Ary), 1 To 6)
        For i = 1 To UBound(Ary)
            If .exists(Ary(i, 4)) Then
                Nary(i, 1) = .Item(Ary(i, 4))(0)
                Nary(i, 2) = .Item(Ary(i, 4))(1)
                Nary(i, 5) = .Item(Ary(i, 4))(2)
                Nary(i, 6) = .Item(Ary(i, 4))(3)
            End If
            If .exists(Ary(i, 1)) Then
                Nary(i, 3) = .Item(Ary(i, 1))(0)
                Nary(i, 4) = .Item(Ary(i, 1))(1)
            End If
        Next i
        Sheet1.Range("AD2").Resize(UBound(Nary), 6).Value = Nary
    End With
End Sub

 

[Fatman003]

Thanks @Fluff, your code works. However, because I was following Domenic code earlier I thought I would be able to edit his code to suit my data. In my workbook, Cell AJ and AK are assigned to a different formula while AL and AM have the column_index as 5 and 6. In short:
(i) AF = VLOOKUP(V2;Sheet2!A:H;7;FALSE) #V is the look_up value, column_index is 7
(i) AG = VLOOKUP(V2;Sheet2!A:H;8;FALSE) #V -lookup, col_index is 8
(ii) AH = VLOOKUP(S2;Sheet2!A:H;7;FALSE) #S is the look_up value, column_index is 7
(iii) AI = VLOOKUP(S2;Sheet2!A:H;8;FALSE) #S - lookup_value, col_index - 8
AJ and AK have another formula (which i have done)
(iii) AL= VLOOKUP(V2;Sheet2!A:H;5;FALSE) #V - look_up value, col_index -5
(iv) AM = VLOOKUP(V2;Sheet2!A:H;6;FALSE) #V - look_up value, col_index - 6

I wasnt able to fully understand your code so I wasnt able to manipulate your code to do it. Any help please?