VLOOKUP for ZIP codes??

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

Try

=VLOOKUP(SUMPRODUCT(--(MinZip<=RealZip),--(MaxZip>=RealZip),MinZip),B30:D31,3,FALSE)

"Dluxe" wrote:

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

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),B30:D31,3,FALSE)


"Duke Carey" wrote:

> Try
>
> =VLOOKUP(SUMPRODUCT(--(MinZip<=RealZip),--(MaxZip>=RealZip),MinZip),B30:D31,3,FALSE)
>
> "Dluxe" wrote:
>
> > 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
> >
> >
> >

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.



"Duke Carey" <DukeCarey@discussions.microsoft.com> wrote in message
news:822CDDD5-383B-4C0D-BA1B-8C2A43345203@microsoft.com...
> 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),B30:D31,3,FALSE)
>
>
> "Duke Carey" wrote:
>
>> Try
>>
>> =VLOOKUP(SUMPRODUCT(--(MinZip<=RealZip),--(MaxZip>=RealZip),MinZip),B30:D31,3,FALSE)
>>
>> "Dluxe" wrote:
>>
>> > 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
>> >
>> >
>> >

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


"Dluxe" wrote:

> 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.
>
>
>
> "Duke Carey" <DukeCarey@discussions.microsoft.com> wrote in message
> news:822CDDD5-383B-4C0D-BA1B-8C2A43345203@microsoft.com...
> > 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),B30:D31,3,FALSE)
> >
> >
> > "Duke Carey" wrote:
> >
> >> Try
> >>
> >> =VLOOKUP(SUMPRODUCT(--(MinZip<=RealZip),--(MaxZip>=RealZip),MinZip),B30:D31,3,FALSE)
> >>
> >> "Dluxe" wrote:
> >>
> >> > 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
> >> >
> >> >
> >> >
>
>
>

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
"Duke Carey" <DukeCarey@discussions.microsoft.com> wrote in message
news:7ADB1EE4-4DAA-42D0-9000-565FA7FEB652@microsoft.com...
> Well, I got sloppy in pasting that formula in. Replace the B30:D31 address
> with the range or range name for your ZipTable. See if that fixes things.
>
>
> "Dluxe" wrote:
>
>> 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.
>>
>>
>>
>> "Duke Carey" <DukeCarey@discussions.microsoft.com> wrote in message
>> news:822CDDD5-383B-4C0D-BA1B-8C2A43345203@microsoft.com...
>> > 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),B30:D31,3,FALSE)
>> >
>> >
>> > "Duke Carey" wrote:
>> >
>> >> Try
>> >>
>> >> =VLOOKUP(SUMPRODUCT(--(MinZip<=RealZip),--(MaxZip>=RealZip),MinZip),B30:D31,3,FALSE)
>> >>
>> >> "Dluxe" wrote:
>> >>
>> >> > 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
>> >> >
>> >> >
>> >> >
>>
>>
>>

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


"Dluxe" wrote:

> 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
> "Duke Carey" <DukeCarey@discussions.microsoft.com> wrote in message
> news:7ADB1EE4-4DAA-42D0-9000-565FA7FEB652@microsoft.com...
> > Well, I got sloppy in pasting that formula in. Replace the B30:D31 address
> > with the range or range name for your ZipTable. See if that fixes things.
> >
> >
> > "Dluxe" wrote:
> >
> >> 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.
> >>
> >>
> >>
> >> "Duke Carey" <DukeCarey@discussions.microsoft.com> wrote in message
> >> news:822CDDD5-383B-4C0D-BA1B-8C2A43345203@microsoft.com...
> >> > 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),B30:D31,3,FALSE)
> >> >
> >> >
> >> > "Duke Carey" wrote:
> >> >
> >> >> Try
> >> >>
> >> >> =VLOOKUP(SUMPRODUCT(--(MinZip<=RealZip),--(MaxZip>=RealZip),MinZip),B30:D31,3,FALSE)
> >> >>
> >> >> "Dluxe" wrote:
> >> >>
> >> >> > 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
> >> >> >
> >> >> >
> >> >> >
> >>
> >>
> >>
>
>
>