Need formula to look up zip codes
 

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

Need formula to look up zip codes
 

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

Need formula to look up zip codes
 

See response in excel.misc

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"mikeburg" wrote in
message ...

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

Need formula to look up zip codes
 

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" wrote in
message ...

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

Need formula to look up zip codes
 

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

Need formula to look up zip codes
 

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" wrote in
message ...

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