Mike,
             The following UDF will return the ZIp Code. It requires a
named range (Street) - column H and anothe called "ZipCode_Table" - columns H
to K, both starting row 1.

HTH

Function GetZipCode(zrng) As String

Dim v(1) As Variant, zTab As Variant

zTab = Range("ZipCode_Table")

n = InStr(1, zrng.Value, " ")
v(0) = Left(zrng.Value, n - 1) ' Street Number
v(1) = Right(zrng.Value, Len(zrng.Value) - n) 'Street Name

ZipCode = Application.Match(v(1), Range("Street"), 0)
If IsError(ZipCode) Then
  GetZipCode = "Street not found"
  Exit Function
End If

nz = Application.CountIf(Range("Street"), v(1))

If nz = 1 Then
  GetZipCode = zTab(ZipCode, 2)
Else
  nrow = ZipCode
  For i = 1 To nz
       If CInt(v(0)) <= zTab(nrow, 4) Then
           GetZipCode = zTab(nrow, 2)
           Exit Function
       End If
  nrow = nrow + 1
  Next i
  End If
End Function
Sub test()
MsgBox GetZipCode(Range("a1"))
End Sub

See response in excel.misc

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

This worked for me and considered both streets with multiple entries and
streets with 0000 0000 in both begin and end.

=IF(VLOOKUP((MID(B2,FIND("
",B2)+1,255)),$H$2:$K$1522,3,FALSE)=0,VLOOKUP((MID(B2,FIND("
",B2)+1,255)),$H$2:$K$1522,2,FALSE),SUMPRODUCT(--($H$2:$H$1522=MID(B2,FIND("
",B2)+1,255)),--((--LEFT(B2,FIND("
",B2)-1))>=$J$2:$J$1522),--((--LEFT(B2,FIND("
",B2)-1))<=$K$2:$K$1522),$I$2:$I$1522))