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)

 

[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

 

[Bob Phillips]

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

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

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

message news:[email protected]...

 

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