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.

 

[christy4370]

LOL... no i see what your looking at! Sorry for the confusion.

No NA is North America not #N/A as in excel can't find a value. Right now it's returning #N/A...

655

172.18.55.44

#N/A

#N/A

<tbody>
</tbody>

testing

172.18.55.0

NA

City Name

<tbody>
</tbody>

 

[Aladin Akyurek]

LOL... no i see what your looking at! Sorry for the confusion.

No NA is North America not #N/A as in excel can't find a value. Right now it's returning #N/A...
[...]

I don't think you do for you are still posting the results of some non-working formula, instead of what you expect to see. See your post #9...

 

[christy4370]

no i expect to see NA and CITY NAME, not #N/A as it is showing now. I have data that is returning correctly but some that is not, using the above INDEX statement. I can't figure out why some data is returning #N/A...

 

[Aladin Akyurek]

no i expect to see NA and CITY NAME, not #N/A as it is showing now. I have data that is returning correctly but some that is not, using the above INDEX statement. I can't figure out why some data is returning #N/A...

I don't want those formula results. I want the true results that must obtain. But if this request is still not intelligible, please just ignore it.

 

[christy4370]

I still need assistance I'm just not sure what i'm confusing you with? I want to see the results not the formula.

 

[Teeroy]

Please provide the full data from a line that is indicating #N/A in sheet1 and the data that you believe it should match on sheet2. This might help in finding the difference.

ps: Remember when I said in an earlier post there are people here far better at Array Formula's then I am? @Aladin Akyurek is one of the best I've ever seen. Help him to help you and you'll get the most optimised solution available with the data provided.

 

[christy4370]

Please provide the full data from a line that is indicating #N/A in sheet1 and the data that you believe it should match on sheet2. This might help in finding the difference.

ps: Remember when I said in an earlier post there are people here far better at Array Formula's then I am? @Aladin Akyurek is one of the best I've ever seen. Help him to help you and you'll get the most optimised solution available with the data provided.

I'm trying. Can I load a file on this site? I think that will help the best...

 

[Teeroy]

You can't attach a file here but if you upload the file to a file storage / sharing site (e.g. Box or Dropbox) and share the file you can post the share hyperlink here.

Remember to sanitise the file so that there is no sensitive data included.

 

[christy4370]

I don't want those formula results. I want the true results that must obtain. But if this request is still not intelligible, please just ignore it.

Here is a link to the file i'm working with. My goal is to get the Region and location columns from the Subnet tab to the Region and Site columns on the Discovery tab.
https://www.dropbox.com/s/b1vx8ee6g2pxhrh/testfile.xlsx?dl=0

 

[FlameRetired]

Not sure I have the concept, but tried this with the upload.

By the way there are duplicate addresses in B12 and B14 of Subnets sheet so Hong Kong is always returned and never Singapore. Was this a typo?

Row\Col	A​	B​	C​	D​	E​
1​	JOB ID​	IP ADDRESS	Region	Site
2​	373​	172.21.120.54	APAC	Hong Kong	=VLOOKUP(LEFT($B2,LOOKUP(1E+306,FIND(".",$B2,ROW($1:$25))))&0,Subnets!$B$2:$D$14,COLUMNS($C:C)+1,0)
3​	374​	172.21.120.55	APAC	Hong Kong
4​	375​	172.21.120.59	APAC	Hong Kong
5​	376​	172.21.120.62	APAC	Hong Kong
6​	377​	172.21.120.63	APAC	Hong Kong
7​	378​	172.21.120.101	APAC	Hong Kong
8​	379​	172.21.120.103	APAC	Hong Kong
9​	380​	172.21.120.104	APAC	Hong Kong
10​	381​	172.21.120.253	APAC	Hong Kong
11​	646​	172.18.55.33	NA	Glendale
12​	647​	172.18.55.35	NA	Glendale
13​	648​	172.18.55.38	NA	Glendale
14​	649​	172.18.55.37	NA	Glendale
15​	650​	172.18.55.36	NA	Glendale
16​	651​	172.18.64.55	NA	Tempe
17​	653​	172.18.55.42	NA	Glendale
18​	654​	172.18.55.43	NA	Glendale
19​	655​	172.18.55.44	NA	Glendale
20​	657​	172.19.16.35	EMEA	London
21​	659​	172.19.65.93	EMEA	London
22​	660​	172.19.65.95	EMEA	London
23​	661​	172.19.65.74	EMEA	London
24​	662​	172.19.65.75	EMEA	London