StartZip EndZip Zone

  • Thread starter Thread starter Jeff Jensen
  • Start date Start date
J

Jeff Jensen

In G5 I want to be able to type a 5 digit zip (such as 55449) code and have
the zone displayed in H5. My table looks like this:

B C D
StartZip EndZip Zone
50001 50499 3
50501 50599 2
50601 51699 3
52001 52099 3
52101 52199 2
52201 52899 3
53001 53299 3
53401 53599 3
53701 53999 3
54001 54099 2
54101 54599 3
54601 54899 2
54901 54999 3
55001 55199 2
55301 56499 2
56501 56799 3
57001 57499 3
57501 57799 4
58001 58299 3
58301 58399 4
58401 58499 3
58501 58899 4
60001 60599 4
60701 60999 4
61001 61499 3
61501 62099 4
62201 62999 4
63001 63199 4
63301 63399 4
68001 68199 3
68301 68599 4
68601 68799 3
68801 68803 4
68818 68818 4
68822 68822 4
68826 68826 4
68845 68845 4
68847 68850 4
68901 68902 4
68949 68949 4
68959 68959 4
69001 69001 4
69101 69101 4
69103 69103 4
69129 69130 4
69138 69138 4
69145 69145 4
69153 69154 4
69160 69160 4
69162 69162 4
69165 69165 4
69190 69190 4
69301 69301 4
69334 69334 4
69336 69336 4
69341 69341 4
69356 69357 4
69361 69361 4
69363 69363 4

Thank you,
Jeff
 
Use VLOOKUP in H5. E.g,

=VLOOKUP(H5,$B$1:$D$1000,3,TRUE)

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]
 
My apologies for not being more clear (It's difficult for me to articulate
some things). VLOOKUP works except for one thing: what if the zip code
entered in G5 isn't in any of the ranges listed in B:C? For example"
B2 = 50001, C2 = 50499
B3 = 50501, C3 = 50599
There is no 50500 in any B:C range, but if I type that zip code in G5, H5
returns 3. I wish it would return some kind of an error.

I got this table from SPEE-DEE Delivery. It's the zones (zip codes) they
deliver to. I need it to exclude the zones they don't deliver to. I'm not
sure how to do that, but an error if it doesn't fall within the range would
be good enough.

Thanks for your help with this,
Jeff

Chip Pearson said:
Use VLOOKUP in H5. E.g,

=VLOOKUP(H5,$B$1:$D$1000,3,TRUE)

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]



In G5 I want to be able to type a 5 digit zip (such as 55449) code and have
the zone displayed in H5. My table looks like this:

B C D
StartZip EndZip Zone
50001 50499 3
50501 50599 2
50601 51699 3
52001 52099 3
52101 52199 2
52201 52899 3
53001 53299 3
53401 53599 3
53701 53999 3
54001 54099 2
54101 54599 3
54601 54899 2
54901 54999 3
55001 55199 2
55301 56499 2
56501 56799 3
57001 57499 3
57501 57799 4
58001 58299 3
58301 58399 4
58401 58499 3
58501 58899 4
60001 60599 4
60701 60999 4
61001 61499 3
61501 62099 4
62201 62999 4
63001 63199 4
63301 63399 4
68001 68199 3
68301 68599 4
68601 68799 3
68801 68803 4
68818 68818 4
68822 68822 4
68826 68826 4
68845 68845 4
68847 68850 4
68901 68902 4
68949 68949 4
68959 68959 4
69001 69001 4
69101 69101 4
69103 69103 4
69129 69130 4
69138 69138 4
69145 69145 4
69153 69154 4
69160 69160 4
69162 69162 4
69165 69165 4
69190 69190 4
69301 69301 4
69334 69334 4
69336 69336 4
69341 69341 4
69356 69357 4
69361 69361 4
69363 69363 4

Thank you,
Jeff
Click to expand...
.
Click to expand...
 
In G5 I want to be able to type a 5 digit zip (such as 55449) code and have
the zone displayed in H5. My table looks like this:

B C D
StartZip EndZip Zone
50001 50499 3
50501 50599 2
50601 51699 3
Click to expand...
Your list of codes is not contiguous. What do you want to do if you enter a
code that is not listed?

The following formula will return either the Zone or, if the Zipcode is not
included, it will return a zero.

I used NAME'd ranges in the formula.

=SUMPRODUCT(-(Zip5>=StartZip),-(Zip5<=EndZip),Zone)
--ron
 
Try this:
=IF(H5>VLOOKUP(H5,$B$1:$D$1000,2,TRUE),"Error",VLOOKUP(H5,$B$1:$D$1000,3,TRUE))

Regards,
Fred

Jeff Jensen said:
My apologies for not being more clear (It's difficult for me to articulate
some things). VLOOKUP works except for one thing: what if the zip code
entered in G5 isn't in any of the ranges listed in B:C? For example"
B2 = 50001, C2 = 50499
B3 = 50501, C3 = 50599
There is no 50500 in any B:C range, but if I type that zip code in G5, H5
returns 3. I wish it would return some kind of an error.

I got this table from SPEE-DEE Delivery. It's the zones (zip codes) they
deliver to. I need it to exclude the zones they don't deliver to. I'm not
sure how to do that, but an error if it doesn't fall within the range
would
be good enough.

Thanks for your help with this,
Jeff

Chip Pearson said:
Use VLOOKUP in H5. E.g,

=VLOOKUP(H5,$B$1:$D$1000,3,TRUE)

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]



In G5 I want to be able to type a 5 digit zip (such as 55449) code and
have
the zone displayed in H5. My table looks like this:

B C D
StartZip EndZip Zone
50001 50499 3
50501 50599 2
50601 51699 3
52001 52099 3
52101 52199 2
52201 52899 3
53001 53299 3
53401 53599 3
53701 53999 3
54001 54099 2
54101 54599 3
54601 54899 2
54901 54999 3
55001 55199 2
55301 56499 2
56501 56799 3
57001 57499 3
57501 57799 4
58001 58299 3
58301 58399 4
58401 58499 3
58501 58899 4
60001 60599 4
60701 60999 4
61001 61499 3
61501 62099 4
62201 62999 4
63001 63199 4
63301 63399 4
68001 68199 3
68301 68599 4
68601 68799 3
68801 68803 4
68818 68818 4
68822 68822 4
68826 68826 4
68845 68845 4
68847 68850 4
68901 68902 4
68949 68949 4
68959 68959 4
69001 69001 4
69101 69101 4
69103 69103 4
69129 69130 4
69138 69138 4
69145 69145 4
69153 69154 4
69160 69160 4
69162 69162 4
69165 69165 4
69190 69190 4
69301 69301 4
69334 69334 4
69336 69336 4
69341 69341 4
69356 69357 4
69361 69361 4
69363 69363 4

Thank you,
Jeff
Click to expand...
.
Click to expand...
Click to expand...
 
Hi Ron,

I named B2:B60 "StartZip" and C2:C60 "EndZip"
Then I entered your formula in H5 and entered a zip code (52103) in G5
I get a #NAME? error.

What am I doing wrong?

To answer your question: If I enter a zip code that's not equal to or
between B & C
(for example:
B2 = 50001, C2 = 50499
B3 = 50501, C3 = 50599
50500 is not equal to or between any of the above)
I want it to display some kind of error.

Thanks,
Jeff
 
Hi Ron,

I named B2:B60 "StartZip" and C2:C60 "EndZip"
Then I entered your formula in H5 and entered a zip code (52103) in G5
I get a #NAME? error.

What am I doing wrong?
Click to expand...

You probably didn't NAME G5 "Zip5"
To answer your question: If I enter a zip code that's not equal to or
between B & C
(for example:
B2 = 50001, C2 = 50499
B3 = 50501, C3 = 50599
50500 is not equal to or between any of the above)
I want it to display some kind of error.
Click to expand...

The formula I gave will return a zero (0) if there is no match, so you could
use an IF statement to check for that and display whatever error message you
wish:

=IF(myFormula=0,"Error Message", myFormula)

--ron
 
Thanks Fred, I works great!

Thanks again,
Jeff

Fred Smith said:
Try this:
=IF(H5>VLOOKUP(H5,$B$1:$D$1000,2,TRUE),"Error",VLOOKUP(H5,$B$1:$D$1000,3,TRUE))

Regards,
Fred

Jeff Jensen said:
My apologies for not being more clear (It's difficult for me to articulate
some things). VLOOKUP works except for one thing: what if the zip code
entered in G5 isn't in any of the ranges listed in B:C? For example"
B2 = 50001, C2 = 50499
B3 = 50501, C3 = 50599
There is no 50500 in any B:C range, but if I type that zip code in G5, H5
returns 3. I wish it would return some kind of an error.

I got this table from SPEE-DEE Delivery. It's the zones (zip codes) they
deliver to. I need it to exclude the zones they don't deliver to. I'm not
sure how to do that, but an error if it doesn't fall within the range
would
be good enough.

Thanks for your help with this,
Jeff

Chip Pearson said:
Use VLOOKUP in H5. E.g,

=VLOOKUP(H5,$B$1:$D$1000,3,TRUE)

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]



On Mon, 11 Jan 2010 14:16:02 -0800, Jeff Jensen

In G5 I want to be able to type a 5 digit zip (such as 55449) code and
have
the zone displayed in H5. My table looks like this:

B C D
StartZip EndZip Zone
50001 50499 3
50501 50599 2
50601 51699 3
52001 52099 3
52101 52199 2
52201 52899 3
53001 53299 3
53401 53599 3
53701 53999 3
54001 54099 2
54101 54599 3
54601 54899 2
54901 54999 3
55001 55199 2
55301 56499 2
56501 56799 3
57001 57499 3
57501 57799 4
58001 58299 3
58301 58399 4
58401 58499 3
58501 58899 4
60001 60599 4
60701 60999 4
61001 61499 3
61501 62099 4
62201 62999 4
63001 63199 4
63301 63399 4
68001 68199 3
68301 68599 4
68601 68799 3
68801 68803 4
68818 68818 4
68822 68822 4
68826 68826 4
68845 68845 4
68847 68850 4
68901 68902 4
68949 68949 4
68959 68959 4
69001 69001 4
69101 69101 4
69103 69103 4
69129 69130 4
69138 69138 4
69145 69145 4
69153 69154 4
69160 69160 4
69162 69162 4
69165 69165 4
69190 69190 4
69301 69301 4
69334 69334 4
69336 69336 4
69341 69341 4
69356 69357 4
69361 69361 4
69363 69363 4

Thank you,
Jeff
.
Click to expand...
Click to expand...

.
Click to expand...
 
Glad I could help. Thanks for the feedback.

Regards,
Fred

Jeff Jensen said:
Thanks Fred, I works great!

Thanks again,
Jeff

Fred Smith said:
Try this:
=IF(H5>VLOOKUP(H5,$B$1:$D$1000,2,TRUE),"Error",VLOOKUP(H5,$B$1:$D$1000,3,TRUE))

Regards,
Fred

Jeff Jensen said:
My apologies for not being more clear (It's difficult for me to
articulate
some things). VLOOKUP works except for one thing: what if the zip code
entered in G5 isn't in any of the ranges listed in B:C? For example"
B2 = 50001, C2 = 50499
B3 = 50501, C3 = 50599
There is no 50500 in any B:C range, but if I type that zip code in G5,
H5
returns 3. I wish it would return some kind of an error.

I got this table from SPEE-DEE Delivery. It's the zones (zip codes)
they
deliver to. I need it to exclude the zones they don't deliver to. I'm
not
sure how to do that, but an error if it doesn't fall within the range
would
be good enough.

Thanks for your help with this,
Jeff

:

Use VLOOKUP in H5. E.g,

=VLOOKUP(H5,$B$1:$D$1000,3,TRUE)

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]



On Mon, 11 Jan 2010 14:16:02 -0800, Jeff Jensen

In G5 I want to be able to type a 5 digit zip (such as 55449) code
and
have
the zone displayed in H5. My table looks like this:

B C D
StartZip EndZip Zone
50001 50499 3
50501 50599 2
50601 51699 3
52001 52099 3
52101 52199 2
52201 52899 3
53001 53299 3
53401 53599 3
53701 53999 3
54001 54099 2
54101 54599 3
54601 54899 2
54901 54999 3
55001 55199 2
55301 56499 2
56501 56799 3
57001 57499 3
57501 57799 4
58001 58299 3
58301 58399 4
58401 58499 3
58501 58899 4
60001 60599 4
60701 60999 4
61001 61499 3
61501 62099 4
62201 62999 4
63001 63199 4
63301 63399 4
68001 68199 3
68301 68599 4
68601 68799 3
68801 68803 4
68818 68818 4
68822 68822 4
68826 68826 4
68845 68845 4
68847 68850 4
68901 68902 4
68949 68949 4
68959 68959 4
69001 69001 4
69101 69101 4
69103 69103 4
69129 69130 4
69138 69138 4
69145 69145 4
69153 69154 4
69160 69160 4
69162 69162 4
69165 69165 4
69190 69190 4
69301 69301 4
69334 69334 4
69336 69336 4
69341 69341 4
69356 69357 4
69361 69361 4
69363 69363 4

Thank you,
Jeff
.
Click to expand...

.
Click to expand...
Click to expand...
 
Ron,

Your first reply works perfectly.
You probably didn't NAME G5 "Zip5"
Click to expand...
Actually, I did name G5 "Zip5", I forgot to name D2:D60 "Zone" :-(
Sorry about that. Anyway, I tried it again and it works great!

Thanks again,
Jeff
 
Ron,

Your first reply works perfectly.
Actually, I did name G5 "Zip5", I forgot to name D2:D60 "Zone" :-(
Sorry about that. Anyway, I tried it again and it works great!

Thanks again,
Jeff
Click to expand...

You're welcome. Glad to help. Thanks for the feedback.
--ron
 
Back
Top