VB Code:
Dim c As Range
Dim MyPostCode As String
Dim PostCodeStart As String
Dim PostCodeEnd As String
MyPostCode = UCase(Range("C8").Value) 'Make it upper case
'UK postcode always ends in 3 characters. Beginning part is variable, and is whatever is left.
PostCodeStart = Left$(MyPostCode, Len(MyPostCode) - 3)
PostCodeEnd = Right$(MyPostCode, 3)
'Find it in the list on sheet 2
Set c = Worksheets("Sheet2").Range("A:A").Find(PostCodeStart, LookIn:=xlValues, LookAt:=xlWhole)
If Not (c Is Nothing) Then 'Found it
MsgBox "Postcode: " & PostCodeStart & " " & PostCodeEnd & Chr(13) & "Location: " & c.Offset(0, 1)
Else 'Not found
GoTo InvalidPostCode:
End If
Exit Sub
'Error message
InvalidPostCode:
MsgBox "Please enter a valid postcode"