Lookup IP address in Subnet, return location

[christy4370]

I have 2 sheets in 1 workbook

Sheet 1

JOB ID	IP ADDRESS	Region	Site
1	172.18.04.104

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

Sheet 2

Display Name	Address	High Range	Region	Location(City)
VLAN MS-AD Domain Contoller	172.18.04.0	172.18.04.256	USA	City Name

<colgroup><col><col span="2"><col><col></colgroup><tbody>
</tbody>

I'm at my wits end trying to get Region and Location from sheet 2 to sheet 1.

 

[Teeroy]

Hi and welcome to the Forum.

I've made a quick brute force formula approach to this problem but there far better users of Array Formula than me here.

I've assumed your data start in A1 on both sheets and allowed for 30 lines of data on Sheet2 (but you can extend if required).
The formulas are:

C2: =INDEX(Sheet2!$D$2:$D$32,MATCH(1,(TRIM(LEFT(SUBSTITUTE(B2,".",REPT(" ",10)),29))=TRIM(LEFT(SUBSTITUTE(Sheet2!$B$2:$B$32,".",REPT(" ",10)),29)))*(TRIM(RIGHT(SUBSTITUTE(B2,".",REPT(" ",5)),3))>=TRIM(RIGHT(SUBSTITUTE(Sheet2!$B$2:$B$32,".",REPT(" ",5)),3)))*(TRIM(RIGHT(SUBSTITUTE(B2,".",REPT(" ",5)),3))<=TRIM(RIGHT(SUBSTITUTE(Sheet2!$C$2:$C$32,".",REPT(" ",5)),3))),0))

D2: =INDEX(Sheet2!$E$2:$E$32,MATCH(1,(TRIM(LEFT(SUBSTITUTE(B2,".",REPT(" ",10)),29))=TRIM(LEFT(SUBSTITUTE(Sheet2!$B$2:$B$32,".",REPT(" ",10)),29)))*(TRIM(RIGHT(SUBSTITUTE(B2,".",REPT(" ",5)),3))>=TRIM(RIGHT(SUBSTITUTE(Sheet2!$B$2:$B$32,".",REPT(" ",5)),3)))*(TRIM(RIGHT(SUBSTITUTE(B2,".",REPT(" ",5)),3))<=TRIM(RIGHT(SUBSTITUTE(Sheet2!$C$2:$C$32,".",REPT(" ",5)),3))),0))

Both are Array Formulas and must be confirmed with CTRL+Shift+Enter (NOT just Enter)

 

[christy4370]

Thank you! Thank you! Thank you!!!

 

[christy4370]

Any idea why about 25% of the IP's came back with an N/A? the others worked great. i did change it to my spreadsheet.

=INDEX(Subnets!$D$2:$D$542,MATCH(1,(TRIM(LEFT(SUBSTITUTE(B675,".",REPT(" ",10)),29))=TRIM(LEFT(SUBSTITUTE(Subnets!$B$2:$B$542,".",REPT(" ",10)),29)))*(TRIM(RIGHT(SUBSTITUTE(B675,".",REPT(" ",5)),3))>=TRIM(RIGHT(SUBSTITUTE(Subnets!$B$2:$B$542,".",REPT(" ",5)),3)))*(TRIM(RIGHT(SUBSTITUTE(B675,".",REPT(" ",5)),3))<=TRIM(RIGHT(SUBSTITUTE(Subnets!$C$2:$C$542,".",REPT(" ",5)),3))),0))

508	172.19.212.255	NA	Humacao
472	172.19.212.38	#N/A	#N/A
473	172.19.212.55	#N/A	#N/A
474	172.19.212.60	#N/A	#N/A
475	172.19.212.69	#N/A	#N/A

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

 

[Teeroy]

Generally this would mean that the IP address does not have a match in the table on sheet2.

The formula has three components to determine a match;

1. where the 1st three numbers match (be careful it's a string match so 19 doesn't equal 019)
2. where the final IP number is greater than or equal to final number of "Address"
3. where the final IP number is less than or equal to final number of "High Range"

If all three conditions are not met then there won't be a match.

 

[Fazza]

An approach using VLOOKUP: could be implemented differently, but I was just wanting to know if this works OK. Perhaps you can test on more data than in thread?
If OK, you could tidy it up. Maybe worth using an extra field to save duplication in columns C & D
cheers

C2: =IF(AND(AND($B2>=VLOOKUP($B2,Sheet2!$B:$B,1),$B2<=VLOOKUP($B2,Sheet2!$B:$C,2),LEN($B2)<=LEN(VLOOKUP($B2,Sheet2!$B:$C,2)))),VLOOKUP($B2,Sheet2!$B:$D,3),NA())
D2: =IF(AND(AND($B2>=VLOOKUP($B2,Sheet2!$B:$B,1),$B2<=VLOOKUP($B2,Sheet2!$B:$C,2),LEN($B2)<=LEN(VLOOKUP($B2,Sheet2!$B:$C,2)))),VLOOKUP($B2,Sheet2!$B:$E,4),NA())

 

[Aladin Akyurek]

It's not a good idea to take a stance that the rule to generate the result is obvious, so no need to spell it. That said, what are the expected results in post #1 and post #4?

 

[Jon von der Heyden]

Hi Christy

Welcome to the forum. I hope you get all the help that you need (although it looks like you're in good hands).

We noticed that you cross-posted this question here: Lookup IP address in Subnet, return location

It's OK, you're new. We don't forbid cross-posting, but we do require you to post references back to your cross posts, so that we know what solutions have been offered, or indeed if your question has been answered. We don't want our members to get frustrated and then not help you again in the future.

There are some links in my signature. Please read the rules and the posting guidelines.

All the best.

Jon

 

[christy4370]

Sorry Aladin i thought i was clear. I need to get the Location and City from Sheet 2 to populate into Region and Site in Sheet 1 where the IP's will not match but be in a range and/or always end in 0. it is working for the most part however some of them as in post #4 are returning N/A even tho they are sitting in the same subnets range. Region will not always be NA, but it will always be column D on sheet 2. More examples

Sheet1

JOB ID	IP ADDRESS	Region	Site
655	172.18.55.44	#N/A	#N/A
651	172.18.64.55	#N/A	#N/A
657	172.19.16.35	#N/A	#N/A
661	172.19.65.74	#N/A	#N/A
662	172.19.65.75	#N/A	#N/A
659	172.19.65.93	#N/A	#N/A
660	172.19.65.95	#N/A	#N/A
381	172.21.120.253	APAC	Singapore
376	172.21.120.62	APAC	Singapore

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

Sheet2

Display Name	Address	Region	Location(City)
Serv342	172.18.82.0	NA	City Name
vlan	172.18.54.0	NA	City Name
testing	172.18.55.0	NA	City Name
dmz	172.18.64.0	NA	City Name
switch	172.18.36.0	NA	City Name
routers	172.18.38.0	NA	City Name
Learning	172.19.16.0	EMEA	where am i
LNC Room	172.19.65.0	EMEA	I'm lost
Wireless	172.21.11.0	APAC	please
Guest Wireless	172.21.120.0	APAC	help
VPN	172.18.98.0	NA	City Name
GUI QAD / PROGRESS DB	172.18.43.0	NA	City Name

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

 

[Aladin Akyurek]

Is #N/A what you expect involving Region for JOB ID = 655 and IP ADDRESS = 172.18.55.44?