I am looking for help with applying VLOOKUP function to add extension to my pre existing table,

TABLE 1:

Table 2:

I am aiming to have the Table B as follows:

sub CARSPEC()

Dim R As Long
Dim C As Long
Table1 = Sheets("Sheet1").Range("A2:A" & Sheets("Sheet1").cells(rows.count,1).end(XLUP).row) 
Table2 = Sheets("Sheet2").Range("A2:H" & Sheets("Sheet1").cells(rows.count,1).end(XLUP).row)
R = Sheets("Sheet1").Range("D2").Row 
C = Sheets("Sheet1").Range("D2").Column
For Each cell In Table1
 Sheets("Sheet1").Cells(R, C) = Application.WorksheetFunction.VLookup(cell, Table2, 2, False)
 R = R   1
Next cell

End sub

With the code above I am only getting the table without the year, meaning :

How could I expand the return values to cover the years as well.

Very much appreciated and THX Ahead.

CodePudding user response：

Sub CARSPEC()

    Dim lastrow As Long, awf
    Dim Table1 As Range, Table2 As Range, cell As Range
    
    With Sheets("Sheet2")
        lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
        Set Table2 = .Range("A2:H" & lastrow)
    End With
    
    Set awf = Application.WorksheetFunction
    With Sheets("Sheet1")
        lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
        Set Table1 = .Range("A2:A" & lastrow)
       
        For Each cell In Table1
            .Cells(cell.Row, "D") = awf.VLookup(cell.Value, Table2, 2, False)
            .Cells(cell.Row, "E") = awf.VLookup(cell.Value, Table2, 3, False)
        Next
    End With
    
End Sub

CodePudding user response：

'R = Sheets("Sheet2").Cells(2, 9).Row
'C = Sheets("Sheet2").Cells(2, 9).Column
'R1 = Sheets("Sheet2").Cells(2, 10).Row
'C1 = Sheets("Sheet2").Cells(2, 10).Column
'R2 = Sheets("Sheet2").Cells(2, 11).Row
'C2 = Sheets("Sheet2").Cells(2, 11).Column

    
    
    'For Each cl In Table1
       
 
        'Sheets("Sheet2").Cells(Dept_Row, Dept_Clm) = Application.WorksheetFunction.VLookup(cl, Table2, 3, 0)
        'Dept_Row = Dept_Row   1
        'Sheets("Sheet2").Cells(Dept_Row1, Dept_Clm1) = Application.WorksheetFunction.VLookup(cl, Table2, 4, 0)
        'Dept_Row1 = Dept_Row1   1
        'Sheets("Sheet2").Cells(Dept_Row2, Dept_Clm2) = Application.WorksheetFunction.VLookup(cl, Table2, 5, 0)
        'Dept_Row2 = Dept_Row2   1
    'Next c1