Lookup in conjunction address range and assign matching data

[anonemous]

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_low	Range_High	Street	Area
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:

Address	Area
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.

 

[kweaver]

This might be one way using 365:

Book2
	A	B	C	D
1	Range_low	Range_High	Street	Area
2	1000	1500	Main St	1
3	1600	1999	Main St	2
4	1000	1500	Oak St	3
5	1600	1999	Oak St	4
6	100	499	Maple St	5
7	500	999	Maple St	6
8	1000	1600	Maple St	7
9
10
11	1004 Main St	1
12	1343 Oak St	3
13	1501 Maple St	7
Sheet1

Cell Formulas
Range	Formula
B11:B13	B11	=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))

 

[Peter_SSs]

Shortening that suggestion up slightly using LET & allowing for if an address is not found

23 07 19.xlsm
	A	B	C	D
1	Range_low	Range_High	Street	Area
2	1000	1500	Main St	1
3	1600	1999	Main St	2
4	1000	1500	Oak St	3
5	1600	1999	Oak St	4
6	100	499	Maple St	5
7	500	999	Maple St	6
8
9
10	Address	Area
11	1004 Main St	1
12	1343 Oak St	3
13	501 Maple St	6
14	1501 Maple St	Unknown
Lookup Area

Cell Formulas
Range	Formula
B11:B14	B11	=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"))

 

[anonemous]

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.

 

[Peter_SSs]

You're welcome. Glad that we have helped you make some progress at least.