Vlookup IP Subnets

[Flame ifrit]

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.

 

[jumbledore]

maybe it's because the formats of the IP SUBNET field are not same. One format could be "numbers" and the other "text"

 

[Flame ifrit]

maybe it's because the formats of the IP SUBNET field are not same. One format could be "numbers" and the other "text"

Tried that i'm afraid, formatted everything as number,text, general. Did not make a dent