Get a location from an IP Address range

[arcsum68]

Hey all,

Definitely not a pro, but I usually can fumble my way through Excel well enough, but alas I need some help.

I am loosing a tool that did this for me, but what I need to do is resolve a location based on an ip from a table of ranges and locations. I extracted our DNS tables from Active Directory and am now trying to figure out how to make this work, but I suspect that the fact that IP's are in octets its screwing up any regular formulas, just not sure how to properly break them up or change them. I have tried a ranged vlookup, but it wont capture most of the IP's. If this has already been answered somewhere, feel free to point me in that direction as I am not finding it. I have attached a sample file, but basically here's what I need to lookup.

Column A Column B Column C Column D Column E Column F
Beginning IP Ending IP Location IP Location Lookup
192.168.0.1 192.168.0.255 Location A 192.168.0.1 Need answer here
201.202.0.0 201.202.0.255 Location B 192.168.0.20 Need answer here
192.168.0.200 Need answer here
201.202.0.75 Need answer here
201.202.0.245 Need answer here


I have Office Pro Plus 2016 and Kutools 16 installed

Thanks all

[arcsum68]

Ugh, that description turned into a mess, sorry about that.

[tim201110]

=vlookup(left(f2,9)&"*",$a$2:$c$18,3,)

[arcsum68]

=vlookup(left(f2,9)&"*",$a$2:$c$18,3,)

Thanks, that only partially works. If there is nothing, or maybe an error it gives me a default of Location A. Also if I change the 3rd octet, it does not appear to recognize it. For example 201.202.010.245 gives me a location of B when it should not match anything.

[arcsum68]

Thanks, that only partially works. If there is nothing, or maybe an error it gives me a default of Location A. Also if I change the 3rd octet, it does not appear to recognize it. For example 201.202.010.245 gives me a location of B when it should not match anything.

Actually if I change that to 201.202.10.245 it works, must be the preceding 0.

Also if I put anything above the range, it's still not working correctly. For example 201.202.0.256 returns location B.

[CK76]

If you want to match anything before last "." exactly. Then use...
=INDEX($C$2:$C$3,MATCH(LEFT(F2,FIND(".",F2,9))&"*",$A$2:$A$3,0))

[arcsum68]

If you want to match anything before last "." exactly. Then use...
=INDEX($C$2:$C$3,MATCH(LEFT(F2,FIND(".",F2,9))&"*",$A$2:$A$3,0))

I am not sure that is looking within the range. For example 192.168.0.256 returns with Location A when it should be nothing.

[samba_ravi]

g2=IFERROR(LOOKUP(2,1/((SUBSTITUTE($A$2:$A$3,LEFT(F2,10),"")+0<=MID($F2,11,10)+0)*(SUBSTITUTE($B$2:$B$3,LEFT(F2,10),"")+0>=MID($F2,11,10)+0)),$C$2:$C$3),"not found")
Try this and copy towards down

[arcsum68]

g2=IFERROR(LOOKUP(2,1/((SUBSTITUTE($A$2:$A$3,LEFT(F2,10),"")+0<=MID($F2,11,10)+0)*(SUBSTITUTE($B$2:$B$3,LEFT(F2,10),"")+0>=MID($F2,11,10)+0)),$C$2:$C$3),"not found")
Try this and copy towards down

Wow, nice. This is really close, but still having issues with in a few scenarios, though I am not really sure why.

Most of my shorter IP's are having a problem where its not finding them, I included a new sample so you can see where some of them are failing but I am not sure why.

[CK76]

You can try below to make it dynamic.
=IFERROR(LOOKUP(2,1/((SUBSTITUTE($A$2:$A$8,LEFT(F2,FIND("^",SUBSTITUTE(F2,".","^",3))),"")+0<=MID($F2,FIND("^",SUBSTITUTE(F2,".","^",3))+1,10)+0)*(SUBSTITUTE($B$2:$B$8,LEFT(F2,FIND("^",SUBSTITUTE(F2,".","^",3))),"")+0>=MID($F2,FIND("^",SUBSTITUTE(F2,".","^",3))+1,10)+0)),$C$2:$C$8),"not found")

However, your last row, has two different value between 2nd "." and last "." in Column A & B.
248 vs 255. Formula won't be able to accommodate for that.

[arcsum68]

I have 40 out of 500 like that, I could make a range for each one. So instead of

141.181.248.0-141.181.255.255

it would be

145.181.248.0 145.181.248.255
145.181.249.0 145.181.249.255
145.181.250.0 145.181.250.255
145.181.251.0 145.181.251.255
145.181.252.0 145.181.252.255
145.181.253.0 145.181.253.255
145.181.254.0 145.181.254.255
145.181.255.0 145.181.255.255

Let me play with that, thanks!

[tim201110]

as a way with helper columns

[arcsum68]

So I suck and didnt get back to you on this because I got sidetracked. Spent today plugging away at the data table I had to create to make the formula work, and it works very well.

Thanks!