VLOOKUP for ZIP codes??

[Dluxe]

Hi all.

I work for a college and we've divided up the country, by zip code, into
many many many different regions.

What I have is a worksheet in excel that looks like this

'MinZip' 'MaxZip' Region
75000 75899 DALLAS
76000 76899 DALLAS

Where MINZIP s the beginning number of a ZIPCode range for a particular
region, and MAX is the terminating zip code.

On a separate form, I have the listing of our applicants and their zip
codes. I was to create a lookup formula that says (in english):
If REALZIP is between MINZIP and MAXZIP on row one, return the region value
for row one.

If I had a straight list of zips in ascending order, it'd be easy with
VLOOKUP:
=VLOOKUP(B2,ZipTable,2)

But I'm not sure how to have it find the row in which the lookup value falls
'between'.

Anyone able to help?? I hate to do this manually.

Thanks, Brian

 

[Guest]

Try

=VLOOKUP(SUMPRODUCT(--(MinZip<=RealZip),--(MaxZip>=RealZip),MinZip),B3031,3,FALSE)

 

[Guest]

The first formula will not work if your ZIP codes are entered as text. If
that's the case, try this one instead:

=VLOOKUP(SUMPRODUCT(--(VALUE(MinZip)<=VALUE(RealZip)),--(VALUE(MaxZip)>=VALUE(RealZip)),MinZip),B3031,3,FALSE)

 

[Dluxe]

Hi Duke,

Thanks for the post... I pasted the formula in and it kicks back an N/A
error. I replaced REALZIP in the equation with a reference to one cell
which contained a zip to lookup. Still kicks back the same error.

My guess is that there's something it doesn't like about the arrays.

 

[Guest]

Well, I got sloppy in pasting that formula in. Replace the B3031 address
with the range or range name for your ZipTable. See if that fixes things.

 

[Dluxe]

No, I caught that... I went through and 're-wrote' the formulas with the
right values. It still crapped out.

Thanks though. I'll pick at it!

B

 

[Guest]

Have you tried the second version of the formula - which wraps the ranges
with the VALUE() function?