If your codes and names are on sheet2 in columns A and B, beginning in row 2:
- Copy the code below.
- Rightclick on the sheettab of sheet1 and click "View code".
- Where the cursor is flashing, rightclick and choose "Paste".
- Now you can drop codes in Sheet1 columnB, beginning in B2.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim a As Integer
If Not Intersect(Range("b2:b30"), Target) Is Nothing Then
a = Application.CountIf(Sheets(2).Range("a2:a30"), Target)
If a > 0 Then
Target.Value = Application.VLookup(Target, Sheets(2).Range("a2:b30"), 2, 0)
End If
End If
End Sub