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
.