Looking up a state based on a zipcode.

[termeric]

hello,

i just got a list of states with thier zip code range, found here --> http://www.structnet.com/search/instructions/zip_min_max_by_state.html

when i get a list of zipcodes, i would liek to be able to search through these ranges and pull the state name into the cell next to it.

can i use the lookup function to do this, or do i need to dive into some VB?


Thnaks,

[Edited by admin ~ fixed the link]

 

[onlyadrafter]

Hello,

Can't access the link!

 

[Von Pookie]

The end was cut off of the url. Add "html" (without the quotes) to the end of that link and it should be fine. I'll edit the post to fix it.

 

[termeric]

thanks von pookie,

the link works now

 

[Norie]

termeric

I copied the data into columns A-E and then used this formula.

=INDEX(B1:B73,MATCH(F1, C1:C73,0),1)

Note I had to use Data>Text to columns... on column C to remove trailing spaces.

 

[texasalynn]

for VB try this

Sub StateZip()
' to lookup zip based on zip code
Dim LR As Long
Dim ZipMatch As Long

LR = ActiveSheet.Range("C65536").End(xlUp).Row
ZipMatch = ActiveCell.Value
ZipState = ActiveCell.Offset(0, -1).Address
For i = 6 To LR
If ZipMatch >= Cells(i, 4) And ZipMatch <= Cells(i, 5) Then
Range(ZipState) = Cells(i, 3)
End If
Next i
End Sub