Vlookup IP Subnets

Flame ifrit

New Member
Joined
Feb 4, 2015
Messages
3
Hello first time poster here, I'll try to be as clear as I can :) I am currently being slowly driven to the brink of madness trying to work out why a vlookup is always returning #NA. The goal is for every IP address listed in a table I need to automatically work out the location based on the subnet.

I have four columns in TABLE A
  • IP ADDRESS: has the IP address.
  • FIRST OCTET: has the first octet using "=IFERROR(LEFT([@[IP Addresses]],FIND(".",[@[IP Addresses]])-1),"-")"
  • SECOND OCTET: has the second octet. using "=IFERROR(LEFT(RIGHT([@[IP Addresses]],(LEN([@[IP Addresses]])-FIND(".",[@[IP Addresses]]))),FIND(".",RIGHT([@[IP Addresses]],(LEN([@[IP Addresses]])-FIND(".",[@[IP Addresses]]))))-1),"-")
  • IP SUBNET: concatenates the two octets into a subnet "=CONCATENATE([@[IP 1st Octet]],".",[@[IP 2nd Octet ]])"

I then have a second table TABLE B with the Subnet and the name of the location that subnet is in.

  • IP SUBNET LOOKUP(AA)
  • LOCATION (AB)

I want to add a fifth column with a vlookup called LOCATION BASED ON SUBNET to table A containing a vlookup that will return location from table B if IP SUBNET & IP SUBNET LOOKUP Match

The problem I have is no matter what I do the vlookup returns an #NA.The vlookup sort of works because if I just type in the subnet in the IP SUBNET Field instead of having it returned by a formula the vlookup works but I need it to be returned by the formula as the data changes all the time.

Thanks in advance.
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
maybe it's because the formats of the IP SUBNET field are not same. One format could be "numbers" and the other "text"
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top