Lookup in conjunction address range and assign matching data

anonemous

New Member
Joined
Dec 2, 2015
Messages
16
Office Version
  1. 365
  2. 2016
  3. 2010
Platform
  1. Windows
  2. MacOS
I'm stumped trying to figure this one out. I have a dataset of about 1,000 addresses and another dataset of all ranges of addresses in a city with an associated area, e.g. Area A, B, C, etc. I'm trying to figure out if there is a way to have excel recognize the range of an address including the street name and return the area in an adjacent column.

Example:

Sheet1
Address
1004 Main St
1343 Oak St
501 Maple St

Sheet2
Range_lowRange_HighStreetArea
1000​
1500​
Main St
1​
1600​
1999​
Main St
2​
1000​
1500​
Oak St
3​
1600​
1999​
Oak St
4​
100​
499​
Maple St
5​
500​
999​
Maple St
6​


Desired Output after looking up from Sheet2 into Sheet1:
AddressArea
1004 Main St
1​
1343 Oak St
3​
1501 Maple St
6​

I know I can do this by geocoding polygons, but I'm trying to see if excel has a way to clean this up than doing this the spatial way.
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
This might be one way using 365:

Book2
ABCD
1Range_lowRange_HighStreetArea
210001500Main St1
316001999Main St2
410001500Oak St3
516001999Oak St4
6100499Maple St5
7500999Maple St6
810001600Maple St7
9
10
111004 Main St1
121343 Oak St3
131501 Maple St7
Sheet1
Cell Formulas
RangeFormula
B11:B13B11=FILTER($D$2:$D$8,(TEXTAFTER(A11," ")=$C$2:$C$8)*((1*TEXTBEFORE(A11," "))>=$A$2:$A$8)*((1*TEXTBEFORE(A11," "))<=$B$2:$B$8))
 
Upvote 1
Shortening that suggestion up slightly using LET & allowing for if an address is not found

23 07 19.xlsm
ABCD
1Range_lowRange_HighStreetArea
210001500Main St1
316001999Main St2
410001500Oak St3
516001999Oak St4
6100499Maple St5
7500999Maple St6
8
9
10AddressArea
111004 Main St1
121343 Oak St3
13501 Maple St6
141501 Maple StUnknown
Lookup Area
Cell Formulas
RangeFormula
B11:B14B11=LET(n,--TEXTBEFORE(A11," "),FILTER(D$2:D$7,(C$2:C$7=TEXTAFTER(A11," "))*(n>=A$2:A$7)*(n<=B$2:B$7),"Unknown"))
 
Upvote 1
Thank both of you for your knowledge! The solutions work perfectly with the expected address ranges.
After running the formula I realized Intersections are a whole different topic (e.g. Main St & Oak St) but thankfully they are a minor issue right now.
 
Upvote 0
You're welcome. Glad that we have helped you make some progress at least. :)
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,182
Members
453,021
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top