Vlookup and Ip Addresses

stuart.munn@angus-it.com · Mar 25th, 2002, 05:06 AM

I am trying to use excel to pull back a hostname using its IP address, yes I know DNS should do this, but our DNS admin people are not very efficient! I have a column of IP addresses and a 2nd column of their equivalent names, when I try to use VLOOKUP to pull back the hostname using the IP address I just get the #N/A error. Is this because excel can't sort IP addresses properly? I've tried formatting the cells as text but that doesn't help. Any info greatly appreciated.

paelinck · Mar 25th, 2002, 05:21 AM

Did you remember to set the range-lookup parameter (the 4th parameter) of the VLOOKUP function to FALSE? Otherwise, the function will expect a sorted range and might give undesired results.

Marc

traversd · Mar 25th, 2002, 05:37 AM

Hi,

A problem I often encounter with VLOOKUP is blank spaces in the cells. Excel treats these as values and thus won't match the variables. I spent ages trying to figure out the problem ! One of the text commands can clean out the blank spaces for you....=TRIM()

stuart.munn@angus-it.com · Mar 25th, 2002, 07:12 AM

Traversd!

You just saved me a lot of grief, it was indeed spaces!

Thanks very much! my mini DNS is now working, now I resolve to get the admin guys on the DNS server to do their job properly.

Cheers!

traversd · Mar 25th, 2002, 10:16 AM

Glad I could give something back to the board for the info I take from it !!!

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 25th, 2002, 05:06 AM	#1
stuart.munn@angus-it.com New Member Join Date: Mar 2002 Location: Scotland Posts: 4	I am trying to use excel to pull back a hostname using its IP address, yes I know DNS should do this, but our DNS admin people are not very efficient! I have a column of IP addresses and a 2nd column of their equivalent names, when I try to use VLOOKUP to pull back the hostname using the IP address I just get the #N/A error. Is this because excel can't sort IP addresses properly? I've tried formatting the cells as text but that doesn't help. Any info greatly appreciated.

Mar 25th, 2002, 05:21 AM	#2
paelinck Board Regular Join Date: Mar 2002 Location: Leiden, The Netherlands Posts: 68	Did you remember to set the range-lookup parameter (the 4th parameter) of the VLOOKUP function to FALSE? Otherwise, the function will expect a sorted range and might give undesired results. Marc

Mar 25th, 2002, 05:37 AM	#3
traversd Board Regular Join Date: Mar 2002 Posts: 82	Hi, A problem I often encounter with VLOOKUP is blank spaces in the cells. Excel treats these as values and thus won't match the variables. I spent ages trying to figure out the problem ! One of the text commands can clean out the blank spaces for you....=TRIM()

Mar 25th, 2002, 07:12 AM	#4
stuart.munn@angus-it.com New Member Join Date: Mar 2002 Location: Scotland Posts: 4	Traversd! You just saved me a lot of grief, it was indeed spaces! Thanks very much! my mini DNS is now working, now I resolve to get the admin guys on the DNS server to do their job properly. Cheers!

Mar 25th, 2002, 10:16 AM	#5
traversd Board Regular Join Date: Mar 2002 Posts: 82	Glad I could give something back to the board for the info I take from it !!!