See if you can adapt this basic code (no error handling)

The code *MUST* go into the worksheet module - *NOT* a standard module.
The code assumes a look up table is kept on another sheet ( in this example sheet2) in the range of A1:Bnnn per your post.

Sheet references in the code are to sheet *CODE NAMES*, not worksheet (tab) names. Adjust if needed.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim lastrow As Long
    Dim rngList As Range
    
    lastrow = Cells(Rows.Count, "A").End(xlUp).Row
    
    Set rngList = Sheet2.Range("A1").CurrentRegion
    
    If Target.Cells.Count > 1 Then Exit Sub
    
    If Not Intersect(Target, Range("B2:B" & lastrow)) Is Nothing Then ' user is in column-B
        If IsNumeric(Target) Then
            Target.Value = Application.WorksheetFunction.VLookup(Target.Value, rngList, 2, False)
        End If
    End If
    
    Set rngList = Nothing

End Sub
This is not the only approach that can be used, but it is simple and does what you asked for.