Excel formula in VBA code

The below code will work if you type in your Code values in sheet2 and highlight them, and run this macro:

Selection.Offset(0, 1).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-1],Sheet1!C[-1]:C,2,FALSE),"""")"
Selection.Offset(0, 2).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-2],Sheet1!C[-2]:C,3,FALSE),"""")"
Selection.Offset(0, 1).Value = Selection.Offset(0, 1).Value
Selection.Offset(0, 2).Value = Selection.Offset(0, 2).Value

Edit: If you are wanting to update values as you type use (thank you @PeterAlbert for added optimisation!):

Private Sub Worksheet_Change(ByVal Target As Range)

    'end if the user made a change to more than one cell at once?
    If Target.Count > 1 Then End

    'stop system activating worksheet_change event while changing the sheet
    Application.EnableEvents = False

    'continue if column 1(A) was updated
    'and
    'dont continue if header or row 1 was changed
    If Target.Column = 1 And Target.Row <> 1 Then

        With Target.Offset(0, 1) 'alter the next cell, current column +1 (column B)

            'RC1 = current row and column 1(A) e.g. if A2 was edited, RC1 = $B2
            'C1:C2 = $A:$B
            .FormulaR1C1 = "=IFERROR(VLOOKUP(RC1,Sheet1!C1:C2,2,FALSE),"""")"
            .Value = .Value 'store value
        End With

        With Target.Offset(0, 2) 'alter the next cell, current column +2 (column C)

            'C1:C3 = $A:$C
            .FormulaR1C1 = "=IFERROR(VLOOKUP(RC1,Sheet1!C1:C3,3,FALSE),"""")"
            .Value = .Value 'store value
        End With

    End If

    Application.EnableEvents = True 'reset system events
End Sub

Explinatioin of RC:

The FormulaR1C1 formula types are good to use when referencing a cell with respect to the current cell. There a few rules to remember:

• The R stands for Row and C is for Column and the integer after it, if any, defines the row or column;
• As a basis the RC formula references itself;
• Any number following the R or C wraped in [] is an offset to itself, e.g. if you are in cell A1 and use R[1]C[1] you would be referencing cell B2;
• Also any number following the R and C is an exact, e.g. if you reference R2C2 no matter the cell you are in would also point to B2; and

To complicate things if you were in cell C5, e.g. using Range("C5").FormulaR1C1 = and coded the follwing:

1. "=RC[-1]" references cell B5
2. "=RC1" references cell A5, more rightly $A5
3. "=R[1]C[-2]" references cell A6
4. "=Sum(C[-1]:C5)" is =Sum(B:E), more rightly =Sum(B:$E)