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]



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

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

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" wrote:

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

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
.

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

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
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(H5VLOOKUP(H5,$B$1:$D$1000,2,TRUE),"Error",VLO OKUP(H5,$B$1:$D$1000,3,TRUE))

Regards,
Fred

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

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

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
.

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

"Ron Rosenfeld" wrote:

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

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

On Tue, 12 Jan 2010 07:28:01 -0800, Jeff Jensen
wrote:

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?

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.


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" wrote:

Try this:
=IF(H5VLOOKUP(H5,$B$1:$D$1000,2,TRUE),"Error",VLO OKUP(H5,$B$1:$D$1000,3,TRUE))

Regards,
Fred

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

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

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
.


.

Glad I could help. Thanks for the feedback.

Regards,
Fred

"Jeff Jensen" wrote in message
...
Thanks Fred, I works great!

Thanks again,
Jeff

"Fred Smith" wrote:

Try this:
=IF(H5VLOOKUP(H5,$B$1:$D$1000,2,TRUE),"Error",VLO OKUP(H5,$B$1:$D$1000,3,TRUE))

Regards,
Fred

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

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

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
.


.

Ron,

Your first reply works perfectly.
You probably didn't NAME G5 "Zip5"
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 Rosenfeld" wrote:

On Tue, 12 Jan 2010 07:28:01 -0800, Jeff Jensen
wrote:

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?

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.


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
.

On Tue, 12 Jan 2010 13:40:01 -0800, Jeff Jensen
wrote:

Ron,

Your first reply works perfectly.
You probably didn't NAME G5 "Zip5"
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

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