Lookup Range Between 2 Numbers & 3rd Value

**dleiman** · 04-27-2010, 12:24 AM

I have DataBase with Streets, Street Numbering and Corresponding ZipCode.
I need to retreive Zipcode for Specified Stree + Number
So to understand below: Street Acosta (2701-3700) but it has different Zip code.
How can I retreive Zip for ACOSTA #3566

Zip Street Name Start End
1407 ACOSTA 1 1300
1407 ACOSTA 1301 2600
1407 ACOSTA 2601 2700
1437 ACOSTA 2701 3400
1437 ACOSTA 3401 3700
1405 ACOYTE 1 200
1405 ACOYTE 201 1100
1414 ACOYTE 1101 1700

Pls see attachement for ref.
I would appreciate some help.

**DonkeyOte** · 04-27-2010, 03:44 AM

You would obviously be best served splitting the street name and number from your criteria cell, however, based on your sample (wherein there is only one space in the string):

=LOOKUP(2;1/((B2:B9=LEFT(G2;FIND(" ";G2&" ")-1))*(C2:C9<=0+REPLACE(G2;1;FIND(" ";G2&" ");"")));A2:A9)

In reality however I think you will need to (read: should) split the street and number (G2 in your file) into two separate cells.

**dleiman** · 04-29-2010, 12:02 PM

There is no problem, I can extract and leave Address "G2" & Number "H2".
I have seen it works only with 1 Name on Address. Sometimes address have 2,3 or 4 words + Street Number
Example: If address is AAAA BBBBB CCCC 777, it doesn´t work.

Any Idea?
Thanks

**dleiman** · 04-29-2010, 12:12 PM

The MLOOKUP works fine only for exact phrases. It´s at: excelguide.eu

**DonkeyOte** · 04-29-2010, 12:29 PM

Originally Posted by dleiman

I have seen it works only with 1 Name on Address. Sometimes address have 2,3 or 4 words + Street Number
Example: If address is AAAA BBBBB CCCC 777, it doesn´t work.

Any Idea?

Yes, see my earlier point re: splitting the street name & number

Given you have now adopted that advice - the formula is "simplified":

=LOOKUP(2;1/((B2:B34=G2)*(C2:C34<=H2)*(D2:D34>=H2));A2:A34)

Note for your example you will get an error given no Zip exists for the parameters specified.