look up zip code

  • Thread starter Thread starter CM
  • Start date Start date
C

CM

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.
 
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
 
You've got confused between text and numbers.
Try =IF(AND(--LEFT(A1,3)>=606,--LEFT(A1,3)<=612),1,0)
 
Back
Top