Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Err:
Dim current As String
Dim update As String
If Target = Range("B4") Then
current = Target.Value
update = "Room " & Application.VLookup(current, ActiveSheet.Range("Names"), 2, 0)
Target.Value = update
End If
Err:
End Sub