I'm trying to deal with 3 digit zip codes. I'm trying to associate prices
with these zip codes, but when I convert a 5 digit zip to 3 digits, I can't
use the result in any vlookup or if satments.

For instance, I convert a zip from 5 to 3 digits using the LEFT function:

=LEFT(A1,3)

Then I try to use the result to determine which range of 3 digit zips the
original zip falls into. So if I want to find out if the first 3 digits fall
in the range 606-612, I tried using this:

=IF(AND(LEFT(A1,3)=606,LEFT(A1,3)<=612),1,0)

But it doesn't return a valid response if those 3 digits fall into that
range of 606-612. Any guidance is appreciated. Thanks!

=IF(AND(LEFT(I1,3)="606",LEFT(I1,3)<="612"),1,0)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"CM" wrote in message
...
I'm trying to deal with 3 digit zip codes. I'm trying to associate prices
with these zip codes, but when I convert a 5 digit zip to 3 digits, I
can't
use the result in any vlookup or if satments.

For instance, I convert a zip from 5 to 3 digits using the LEFT function:

=LEFT(A1,3)

Then I try to use the result to determine which range of 3 digit zips the
original zip falls into. So if I want to find out if the first 3 digits
fall
in the range 606-612, I tried using this:

=IF(AND(LEFT(A1,3)=606,LEFT(A1,3)<=612),1,0)

But it doesn't return a valid response if those 3 digits fall into that
range of 606-612. Any guidance is appreciated. Thanks!

The left function returns text but you are comparing that to a number... Add
some quotation marks...

=IF(AND(LEFT(A1,3)="606",LEFT(A1,3)<="612"),1,0)
or just
=AND(LEFT(A1,3)="606",LEFT(A1,3)<="612")
which will return true or false as the case warrants.

--
HTH...

Jim Thomlinson


"CM" wrote:

I'm trying to deal with 3 digit zip codes. I'm trying to associate prices
with these zip codes, but when I convert a 5 digit zip to 3 digits, I can't
use the result in any vlookup or if satments.

For instance, I convert a zip from 5 to 3 digits using the LEFT function:

=LEFT(A1,3)

Then I try to use the result to determine which range of 3 digit zips the
original zip falls into. So if I want to find out if the first 3 digits fall
in the range 606-612, I tried using this:

=IF(AND(LEFT(A1,3)=606,LEFT(A1,3)<=612),1,0)

But it doesn't return a valid response if those 3 digits fall into that
range of 606-612. Any guidance is appreciated. Thanks!

LEFT will return a text result, so try it this way:

=IF(AND(LEFT(A1,3)*1=606,LEFT(A1,3)*1<=612),1,0)

Hope this helps.

Pete


"CM" wrote in message
...
I'm trying to deal with 3 digit zip codes. I'm trying to associate prices
with these zip codes, but when I convert a 5 digit zip to 3 digits, I
can't
use the result in any vlookup or if satments.

For instance, I convert a zip from 5 to 3 digits using the LEFT function:

=LEFT(A1,3)

Then I try to use the result to determine which range of 3 digit zips the
original zip falls into. So if I want to find out if the first 3 digits
fall
in the range 606-612, I tried using this:

=IF(AND(LEFT(A1,3)=606,LEFT(A1,3)<=612),1,0)

But it doesn't return a valid response if those 3 digits fall into that
range of 606-612. Any guidance is appreciated. Thanks!

You've got confused between text and numbers.
Try =IF(AND(--LEFT(A1,3)=606,--LEFT(A1,3)<=612),1,0)
--
David Biddulph

"CM" wrote in message
...
I'm trying to deal with 3 digit zip codes. I'm trying to associate prices
with these zip codes, but when I convert a 5 digit zip to 3 digits, I
can't
use the result in any vlookup or if satments.

For instance, I convert a zip from 5 to 3 digits using the LEFT function:

=LEFT(A1,3)

Then I try to use the result to determine which range of 3 digit zips the
original zip falls into. So if I want to find out if the first 3 digits
fall
in the range 606-612, I tried using this:

=IF(AND(LEFT(A1,3)=606,LEFT(A1,3)<=612),1,0)

But it doesn't return a valid response if those 3 digits fall into that
range of 606-612. Any guidance is appreciated. Thanks!