Sub TEST()

    Dim RowsCountSheet1 As String
    Dim RowsCountData As String
    
    Sheets("Sheet1").Select
    Set Rng = Range("B1:B" & [B1:B900000].Find("*", , , , , xlPrevious).Row)
    With WorksheetFunction
        iNonBlank = .CountA(Rng)
        iBlank = .CountBlank(Rng)
    End With
    RowsCountSheet1 = iNonBlank
    Sheets("Data").Select
    Set Rng = Range("B1:B" & [B1:B900000].Find("*", , , , , xlPrevious).Row)
    With WorksheetFunction
        iNonBlank = .CountA(Rng)
        iBlank = .CountBlank(Rng)
    End With
    RowsCountData = iNonBlank

    'RowsCountSheet1 = Sheets("Sheet1").UsedRange.Rows.Count
    'RowsCountData = Sheets("Data").UsedRange.Rows.Count

    Range("AC2").Select
    ActiveCell.FormulaR1C1 = _
        "=IFNA(VLOOKUP(RC[-27],Sheet1!R1C1:R" & RowsCountSheet1 & "C4,2,FALSE),RC[-26])"
    Range("AD2").Select
    ActiveCell.FormulaR1C1 = _
        "=IFNA(VLOOKUP(RC[-28],Sheet1!R1C1:R" & RowsCountSheet1 & "C4,3,FALSE),RC[-26])"
    Range("AC2:AD2").Select
    Selection.AutoFill Destination:=Range("AC2:AD" & RowsCountData & "")
    Range("AC2:AD" & RowsCountData & "").Select
    Selection.Copy
    Range("C2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("D3").Select
    Columns("AC:AD").Select
    Selection.Delete Shift:=xlToLeft
End Sub