Need formula to look up zip codes

[mikeburg]

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

[Toppers]

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
>
>

[Bob Phillips]

See response in excel.misc

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"mikeburg" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Tom Ogilvy]

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))

--
Regards,
Tom Ogilvy


"mikeburg" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[mikeburg]

You guys are a great help. In trying to decide which way to go, what would be VBA code to accomplish arriving at the zip code. If possible, I would like the VBA code to be the sheets change code. When the street is keyed in, the corresponding zip code is looked up & put by it.

Don't give up on me. I am learning a lot from y'all. mikeburg

[Tom Ogilvy]

Right click on the sheet tab and select view code. Put in code like this:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim v As Variant, sVal As String
Dim sStr As String, lVal As Long
Dim sName As String, i As Long
If Target.Address = "$B$2" Then
v = Range("$H$2:$K$1522")
sVal = Left(Target, InStr(1, _
Target.Value, " ", vbTextCompare) - 1)
lVal = CLng(sVal)
sName = Right(Target, Len(Target) - (Len(sVal) + 1))
sStr = "Not Found"
For i = 1 To UBound(v, 1)
If StrComp(sName, v(i, 1), vbTextCompare) = 0 Then
If CLng(v(i, 3)) = 0 Or _
(lVal >= CLng(v(i, 3)) And _
lVal <= CLng(v(i, 4))) Then
sStr = Format(v(i, 2), "00000")
Exit For
End If
End If
Next
Range("B3").Value = sStr
End If
End Sub


--
Regards,
Tom Ogilvy

"mikeburg" <[email protected]> wrote in
message news:[email protected]...
>
> You guys are a great help. In trying to decide which way to go, what
> would be VBA code to accomplish arriving at the zip code. If possible,
> I would like the VBA code to be the sheets change code. When the street
> is keyed in, the corresponding zip code is looked up & put by it.
>
> Don't give up on me. I am learning a lot from y'all. mikeburg
>
>
> --
> mikeburg
> ------------------------------------------------------------------------
> mikeburg's Profile:
http://www.excelforum.com/member.php...o&userid=24581
> View this thread: http://www.excelforum.com/showthread...hreadid=503623
>