Find Matching Zipcode Range for Address

[Luce]

Hi,
I need some help finding a way to map a zipcode to a sales rep, based on a
match within a zipcode range assignment.

My lookup table has a ZipLow and ZipHigh value, with a Rep Id assigned to
the zipcode range. The zips are both US and Canadian Zip ranges

I have a 2nd worksheet that lists a specific address, and I want to compare
the zip of the address listed on this worksheet to see which rep would be
assigned based on the lookup table below.

Sample lookup table:

RepID ZipLow ZipHigh Country
Mark 33301 33999 US
Jim 34401 34999 US
Sam 35501 35999 US
Jill 47701 47999 US
Sue 48222 48999 US
Amy 60001 60999 US
John K0A 0A0 K9Z 9Z9 Canada
Jay L0A 0A0 P9Z 9Z9 Canada

Sample Worksheet that I need to populate with appropriate RepID:

Address/Zip Rep Assignment
60101 Amy
47801 Jill
P9Z 9Z6 Jay

I tried using Vlookup function with approximate match set with TRUE. It did
not work correctly in all cases.

Any suggestions????

Thanks so much,
Luce

 

[Jacob Skaria]

I have tried out the below in the same sheet itself. Assuming the below data
lies in A19 with headers in row1..

F2 = 60101
F3 = P9Z 9Z6

Enter the below formula in G2. Please try and feedback

=INDEX($A$1:$A$25,SUMPRODUCT(--($B$1:$B$25<=F2),--($C$1:$C$25>=F2),--ROW($B$1:$B$25)),1)


If this post helps click Yes

 

[Luce]

This formula works great! Very interesting approach!

Thank you!

 

[joel]

The code below actually gets the loacation where the zip codes are located by
going to the US post office website. I can also write a program that wil
find the zip code from the address (never need this feature).

Sub PostCodes()


POSTCODE = InputBox("Enter PostCode (NW12 9BN): ")
If POSTCODE = "" Then
POSTCODE = "NW12 9BN"
End If

PHONENUMBER = InputBox("Enter Phone Number (02071234567): ")
If PHONENUMBER = "" Then
PHONENUMBER = "02071234567"
End If

Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True

URL = "http://www.orange.co.uk/partner-savc/default.cfm"


'get web page
IE.Navigate2 URL
Do While IE.readyState <> 4
DoEvents
Loop

Do While IE.busy = True
DoEvents
Loop

Set Form = IE.document.getElementsByTagname("Form")
Set Inputform = Form.Item(0)

Set PhoneNumbox = Inputform.Item(0)
PhoneNumbox.Value = PHONENUMBER

Set PostCodebox = Inputform.Item(1)
PostCodebox.Value = POSTCODE


Set POSTCODEbutton = IE.document.getElementById("theForm")
POSTCODEbutton.onsubmit = POSTCODEbutton.action
POSTCODEbutton.submit

'POSTCODEbutton.Click
Do While IE.busy = True
DoEvents
Loop

'Set Table = IE.document.getElementsByTagname("Table")
'Location = Table(0).Rows(2).innertext
IE.Quit
'MsgBox ("Zip code = " & POSTCODE & " City/State = " & Location)


End Sub