Look up specific street address within street address range

Hello,

I have an excel problem that I cannot figure out...in Tab#1 I have list of addresses...example 217 11TH ST LOS ANGELES 90015....Tab #2 has list of address ranges...example 201 - 217 11TH ST LOS ANGELES 90015, 221 - 221 11TH ST LOS ANGELES 90015...How can I see if address in tab #1 is covered by address ranges on Tab #2? Any help would be greatly appreciated and valued.

Test.PNG

With extra column in Tab 2


Kind regards
Leo

Hello Leo

Thank you so much for looking into it. However, I need to see if street number is within range...if 217 is within ranges 201-217 or 221-221. Not just street name and zip. Once again thank you for your help.

I see,

and the result in the exemple has to be Yes Yes or Yes No ?

Kind regards
Leo

Tab #1, Column F (YES/NO) or (1/0) or any other indicator.

more like this ?

Hello Leo

It should have both "Yes" since it was covered in first range 201 - 217 in Tab # 2. Also, complete file will have to look up few thousand of those streets within different ranges. So those streets and ranges will not directly correlate A2, B2 on tab #1 to A2, B2 on tab # 2...their position fixed in column not in line.

thats wy i did ask if the result has to be yes yes or yes no

in this it is 2 times yes, but then it is with extra column


Kind regards
Leo

My fault for confusion...it seems to work. However, if I change cell A3 on Tab #1 from 217 to 245, "YES" does not change to "NO", but it is outside of any range on Tab # 2. Again, thank you for taking your time on this one.

Try this one

=IF(COUNTIFS('Tab 2'!G:G,E2,'Tab 2'!E:E,D2,'Tab 2'!D:D,C2,'Tab 2'!C:C,B2,'Tab 2'!B:B,">="&A2,'Tab 2'!A:A,"<="&A2),"YES","NO")

My mistake

1 > to much
1 < to little

Originally Posted by dbd6lsx

when I put 221...it still says "NO". Not sure how to trigger it to skip 1st range and jump to next one...

I guess that is directed at LeoTaxi because my suggestion says "YES" when you put 221.

indeed, takes always the first match so not a good one then.

Jason...it worked perfectly. This is AMAZING!!!. THANK YOU so much Jason and Leo for taking your time and looking into it. I truly appreciate it. Have a great day.