Formula to look up zip codes

[mikeburg]

Need formula to look up the zip codes listed in H2 thru K1522 when an address is entered. For example:

In Cell B2 the following is entered:
2005 Airline Rd

Cell B3 return zip code:
75605

Zip code list:

__|____H____|___I__ |__J__|__K___
_1|_Street___|__Zip__|Begin|_End__
10| Adrian Rd | 75605 | 0000 | 0000
11| Agness Dr| 75602 | 0000 | 0000
12| Airline Rd | 75603 | 0001 | 1999
13| Airline Rd | 75605 | 2000 | 9999
14| Akinships | 75605 | 0000 | 0000
15| Albertata | 75605 | 0000 | 0000
16| Aledo Str | 75604 | 0000 | 0000
17| Alexander | 75604 | 0000 | 0000

The street name must not only be looked up, but if begin & end numbers exists in colums J & K, they must be used to determine the zip code too.

Thanks so very much for your help. mikeburg

[Bob Phillips]

=VLOOKUP(MID(B2,FIND(" ",B2)+1,255),$H$2:$K$52,2,FALSE)

although this does assume that the address in B2 is always
number/space/street

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"mikeburg" <mikeburg.21zrxm_1137858900.9508@excelforum-nospam.com> wrote in
message news:mikeburg.21zrxm_1137858900.9508@excelforum-nospam.com...
>
> Need formula to look up the zip codes listed in H2 thru K1522 when an
> address is entered. For example:
>
> In Cell B2 the following is entered:
> 2005 Airline Rd
>
> Cell B3 return zip code:
> 75605
>
> Zip code list:
>
> __|____H____|___I__ |__J__|__K___
> _1|_Street___|__Zip__|Begin|_End__
> 10| Adrian Rd | 75605 | 0000 | 0000
> 11| Agness Dr| 75602 | 0000 | 0000
> 12| Airline Rd | 75603 | 0001 | 1999
> 13| Airline Rd | 75605 | 2000 | 9999
> 14| Akinships | 75605 | 0000 | 0000
> 15| Albertata | 75605 | 0000 | 0000
> 16| Aledo Str | 75604 | 0000 | 0000
> 17| Alexander | 75604 | 0000 | 0000
>
> The street name must not only be looked up, but if begin & end numbers
> exists in colums J & K, they must be used to determine the zip code
> too.
>
> Thanks so very much for your help. mikeburg
>
>
> --
> mikeburg
> ------------------------------------------------------------------------
> mikeburg's Profile:
http://www.excelforum.com/member.php...o&userid=24581
> View this thread: http://www.excelforum.com/showthread...hreadid=503642
>

[Bob Phillips]

Sorry, seems I missed the bit about the street number.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
news:eVLvnWqHGHA.3144@TK2MSFTNGP11.phx.gbl...
> =VLOOKUP(MID(B2,FIND(" ",B2)+1,255),$H$2:$K$52,2,FALSE)
>
> although this does assume that the address in B2 is always
> number/space/street
>
> --
>
> HTH
>
> Bob Phillips
>
> (remove nothere from the email address if mailing direct)
>
> "mikeburg" <mikeburg.21zrxm_1137858900.9508@excelforum-nospam.com> wrote
in
> message news:mikeburg.21zrxm_1137858900.9508@excelforum-nospam.com...
> >
> > Need formula to look up the zip codes listed in H2 thru K1522 when an
> > address is entered. For example:
> >
> > In Cell B2 the following is entered:
> > 2005 Airline Rd
> >
> > Cell B3 return zip code:
> > 75605
> >
> > Zip code list:
> >
> > __|____H____|___I__ |__J__|__K___
> > _1|_Street___|__Zip__|Begin|_End__
> > 10| Adrian Rd | 75605 | 0000 | 0000
> > 11| Agness Dr| 75602 | 0000 | 0000
> > 12| Airline Rd | 75603 | 0001 | 1999
> > 13| Airline Rd | 75605 | 2000 | 9999
> > 14| Akinships | 75605 | 0000 | 0000
> > 15| Albertata | 75605 | 0000 | 0000
> > 16| Aledo Str | 75604 | 0000 | 0000
> > 17| Alexander | 75604 | 0000 | 0000
> >
> > The street name must not only be looked up, but if begin & end numbers
> > exists in colums J & K, they must be used to determine the zip code
> > too.
> >
> > Thanks so very much for your help. mikeburg
> >
> >
> > --
> > mikeburg
> > ------------------------------------------------------------------------
> > mikeburg's Profile:
> http://www.excelforum.com/member.php...o&userid=24581
> > View this thread:
http://www.excelforum.com/showthread...hreadid=503642
> >
>
>

[Peo Sjoblom]

One possible way

=INDEX($I$2:$I$9000,MATCH(1,($H$2:$H$9=E11)*($J$2:$J$9<=SUBSTITUTE(B2,LEFT(B2,FIND("
",B2)),""))*($K$2:$K$9>=--LEFT(B2,FIND(" ",B2)-1)),0))


entered with ctrl + shift & enter


make sure there are no hidden spaces in the table

--
Regards,

Peo Sjoblom

Portland, Oregon




"mikeburg" <mikeburg.21zrxm_1137858900.9508@excelforum-nospam.com> wrote in
message news:mikeburg.21zrxm_1137858900.9508@excelforum-nospam.com...
>
> Need formula to look up the zip codes listed in H2 thru K1522 when an
> address is entered. For example:
>
> In Cell B2 the following is entered:
> 2005 Airline Rd
>
> Cell B3 return zip code:
> 75605
>
> Zip code list:
>
> __|____H____|___I__ |__J__|__K___
> _1|_Street___|__Zip__|Begin|_End__
> 10| Adrian Rd | 75605 | 0000 | 0000
> 11| Agness Dr| 75602 | 0000 | 0000
> 12| Airline Rd | 75603 | 0001 | 1999
> 13| Airline Rd | 75605 | 2000 | 9999
> 14| Akinships | 75605 | 0000 | 0000
> 15| Albertata | 75605 | 0000 | 0000
> 16| Aledo Str | 75604 | 0000 | 0000
> 17| Alexander | 75604 | 0000 | 0000
>
> The street name must not only be looked up, but if begin & end numbers
> exists in colums J & K, they must be used to determine the zip code
> too.
>
> Thanks so very much for your help. mikeburg
>
>
> --
> mikeburg
> ------------------------------------------------------------------------
> mikeburg's Profile:
> http://www.excelforum.com/member.php...o&userid=24581
> View this thread: http://www.excelforum.com/showthread...hreadid=503642
>

[Tom Ogilvy]

This worked for me and considered both streets with multiple entries and
streets with 0000 0000 in both begin and end.

=IF(VLOOKUP((MID(B2,FIND("
",B2)+1,255)),$H$2:$K$1522,3,FALSE)=0,VLOOKUP((MID(B2,FIND("
",B2)+1,255)),$H$2:$K$1522,2,FALSE),SUMPRODUCT(--($H$2:$H$1522=MID(B2,FIND("
",B2)+1,255)),--((--LEFT(B2,FIND("
",B2)-1))>=$J$2:$J$1522),--((--LEFT(B2,FIND("
",B2)-1))<=$K$2:$K$1522),$I$2:$I$1522))

--
Regards,
Tom Ogilvy

"mikeburg" <mikeburg.21zrxm_1137858900.9508@excelforum-nospam.com> wrote in
message news:mikeburg.21zrxm_1137858900.9508@excelforum-nospam.com...
>
> Need formula to look up the zip codes listed in H2 thru K1522 when an
> address is entered. For example:
>
> In Cell B2 the following is entered:
> 2005 Airline Rd
>
> Cell B3 return zip code:
> 75605
>
> Zip code list:
>
> __|____H____|___I__ |__J__|__K___
> _1|_Street___|__Zip__|Begin|_End__
> 10| Adrian Rd | 75605 | 0000 | 0000
> 11| Agness Dr| 75602 | 0000 | 0000
> 12| Airline Rd | 75603 | 0001 | 1999
> 13| Airline Rd | 75605 | 2000 | 9999
> 14| Akinships | 75605 | 0000 | 0000
> 15| Albertata | 75605 | 0000 | 0000
> 16| Aledo Str | 75604 | 0000 | 0000
> 17| Alexander | 75604 | 0000 | 0000
>
> The street name must not only be looked up, but if begin & end numbers
> exists in colums J & K, they must be used to determine the zip code
> too.
>
> Thanks so very much for your help. mikeburg
>
>
> --
> mikeburg
> ------------------------------------------------------------------------
> mikeburg's Profile:
http://www.excelforum.com/member.php...o&userid=24581
> View this thread: http://www.excelforum.com/showthread...hreadid=503642
>