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

=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" wrote in
message ...

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

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" wrote in
message ...

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

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

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Bob Phillips" wrote in message
...
=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" wrote
in
message ...

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

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" wrote in
message ...

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