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
"mikeburg" wrote:


Need formula to look up the zip codes listed in H2 thru K1522 when an
address is entered. For example:

In Cell B2 the following is entered:
2005 Airline Rd

Cell B3 return zip code:
75605

Zip code list:
_
__|____H____|___I___|__J__|__K___
_1|_Street___|__Zip__|Begin|_End__
10|_Adrian_Rd_|_75605_|_0000_|_0000
11|_Agness_Dr|_75602_|_0000_|_0000
12|_Airline_Rd_|_75603_|_0001_|_1999
13|_Airline_Rd_|_75605_|_2000_|_9999
14|_Akinships_|_75605_|_0000_|_0000
15|_Albertata_|_75605_|_0000_|_0000
16|_Aledo_Str_|_75604_|_0000_|_0000
17|_Alexander_|_75604_|_0000_|_0000_

The street name must not only be looked up, but if begin & end numbers
exists in colums J & K, they must be used to determine the zip code
too.

Thanks so very much for your help. mikeburg


--
mikeburg
------------------------------------------------------------------------
mikeburg's Profile: http://www.excelforum.com/member.php...o&userid=24581
View this thread: http://www.excelforum.com/showthread...hreadid=503623